filling down to last cell in row

[chriss09]

I've seen so many fill down codes but have had no success in making them work for my situation. Below is a recorded macro. I'm trying to edit the part when it fills down. I need it to select cells C2:H2 down to the last row of the sheet (i guess based on column A, this will always have data to the bottom of the active sheet with no gaps)

Sub Macro2()
'
' Conditional Formatting fills right then down
'
'
    Range("C2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=L2<=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C2:H2").Select
    Selection.FillRight
'this part below
    Selection.AutoFill Destination:=Range("C2:H130"), Type:=xlFillDefault
    Range("C2:H130").Select
End Sub

Please show me the way

 

[Smitty]

Welcome to the Board!

See if this does it:

<font face=Calibri>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    LR = Cells(Rows.Count, "A").End(xlUp).Row<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("C2:H2")<br>        .FillRight<br>        .AutoFill Destination:=Range("C2:H" & LR), Type:=xlFillDefault<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br></FONT>

HTH,

 

[chriss09]

Perfect!!!!! Thank you so much. Just started messing with vba and have been searching for that part for two days....THANK YOU!!!

 

[Smitty]

Next time you'll know to check here first.