Autofill Until is Empty

Hi, Dnass,

maybe you should have another look at my posts in this thread for finding out what was changed in order to make the code work without error. You used the very same new Variable as in the opening post - why? The code by Sixthsense works with a With-Statement but neglects the Autofill range to be related to that worksheet.

Regarding your question: put in another if to check if the value of the Variable holding the last filled row in Column A is greater 2 (I choose <=2 here).

Sub AAA_1()

Dim lngLR As Long

With Worksheets("07DS")
    lngLR = .Range("A" & Rows.Count).End(xlUp).Row
    If lngLR <= 2 Then Exit Sub
    If .Range("E2").Value <> "" Then .Range("E2").AutoFill Destination:=.Range("E2:E" & lngLR), Type:=xlFillCopy
    If .Range("F2").Value <> "" Then .Range("F2").AutoFill Destination:=.Range("F2:F" & lngLR), Type:=xlFillCopy
    If .Range("G2").Value <> "" Then .Range("G2").AutoFill Destination:=.Range("G2:G" & lngLR), Type:=xlFillCopy
    If .Range("H2").Value <> "" Then .Range("H2").AutoFill Destination:=.Range("H2:H" & lngLR), Type:=xlFillCopy
    If .Range("I2").Value <> "" Then .Range("I2").AutoFill Destination:=.Range("I2:I" & lngLR), Type:=xlFillCopy
End With
End Sub

This code will check each single column before filling.

You may combine this to read

Sub AAA_2()

Dim lngLR As Long

With Worksheets("07DS")
    lngLR = .Range("A" & Rows.Count).End(xlUp).Row
    If lngLR <= 2 Then Exit Sub
    If .Range("E2").Value <> "" Then .Range("E2:i2").AutoFill Destination:=.Range("E2:I" & lngLR), Type:=xlFillCopy
End With
End Sub

HTH,
Holger