View Full Version : Find 1st cell under header, and fill down vs adjacent column
Chris_AAA
06-04-2015, 09:07 AM
Hi
Wondered if anyone is kind enough to advise how to improve my code to perform the function I would like to achieve..
ActiveSheet.Columns("A:BG").AutoFilter Field:=9, Criteria1:=" "
With Worksheets("TEST").AutoFilter.Range
Range("I" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
ActiveCell.FormulaR1C1 = "B"
Then to fill down to last known row in relation to field:=10
Thank you kindly.
Sixthsense..
06-04-2015, 09:19 PM
Check whether this solves it :)
Dim lRw As Long
ActiveSheet.AutoFilterMode = False
lRw = Range("I" & Rows.Count).End(xlUp).Row
With Columns("A:BG")
.AutoFilter Field:=9, Criteria1:=" "
On Error Resume Next
Range("I" & lRw).SpecialCells(xlCellTypeVisible).Value = "B"
On Error GoTo 0
End With
ActiveSheet.AutoFilterMode = False
Chris_AAA
06-05-2015, 02:58 AM
Hey Sixthsense thanks for the feedback.
It almost works...
however rather than only replace blank with B on just items in column I, it actually replaces every cell in the row - from a:bg Vertically. I want the code to only replace blanks in column 'I' only.
any thoughts?
Thanks so much for taking the time to read my post and respond
Sixthsense..
06-05-2015, 03:06 AM
Replace
Criteria1:=" "
With
Criteria1:="="
Chris_AAA
06-05-2015, 03:21 AM
Hmmmm its weird, the code seems to find blank, replace blank in range with B, but then also every single cell in that row, also populates with B.
Also the header is now all 'B's all the way to the end of excel (xfd)
Chris_AAA
06-05-2015, 04:52 AM
Figured out what i needed to do :-)
Columns("I:I").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Replace What:="", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Thnak you kindly for your help above though, it got the mind working :-)
mancubus
06-05-2015, 05:08 AM
Chris_AAA
avoid using Selec's and Activate's. you rarely need them.
Range("I2:I500").SpecialCells(xlCellTypeBlanks).Replace "", Replacement:="B", LookAt:=xlPart
or you can directly write a function's parameters' values without writing the parameter name. just make sure to write the values in order.
Range("I2:I500").SpecialCells(xlCellTypeBlanks).Replace "What:="", "B", xlPart
since you are filling blank cells in as certain calumn with a specific value, you onlu need
Range("I2:I500").SpecialCells(xlCellTypeBlanks).Value = "B"
p45cal
06-05-2015, 06:22 AM
but then also every single cell in that row, also populates with B.Ths is because in SixthSense's line:
Range("I" & lRw).SpecialCells(xlCellTypeVisible).Value = "B"
the Range("I" & lRw) is a single cell, and when .Specialcells is applied to a single cell, the whole sheet's specialcells are returned.
Changing the line to Range("I2:I" & lRw).SpecialCells(xlCellTypeVisible).Value = "B"
might do it for you if lRw is never 2 (when column I only has a header and a single value in row 2 and is otherwise blank).
Sixthsense..
06-05-2015, 09:07 PM
Ths is because in SixthSense's line:Range("I" & lRw) is a single cell
Oopss.. wondering how I written the code in that way... :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.