PDA

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... :)