Autofill Until is Empty

Help!!

Here is my code

Sub AAA()
Dim lastrow As Long
     
    lastrow = Worksheets("07DS").Range("A2").End(xlDown).Row
    With Worksheets("07DS").Range("E2")
    
    If Range("B2") = "" Then
    Exit Sub
    Else
            .AutoFill Destination:=Range("E2:E" & lastrow&), Type:=xlFillCopy
End If
End With
End Sub

What is meant to happen is that Column E is autofilled until column A is empty! However this is not working column E is Autofilling until the end of the sheet.

Any help with where I went wrong in my code? PS column A will have a dynamic list, that is each time it could have 1 or 100 rows depending thats why I need E to autofill until A is blank.

Thank you

Try this…

Sub AAA()
Dim lastRow As Long
     
lastRow = Worksheets("07DS").Range("A" & Rows.Count).End(xlUp).Row

With Worksheets("07DS").Range("E2")
    If .Value <> "" Then .AutoFill Destination:=Range("E2:E" & lastRow), Type:=xlFillCopy
End With

End Sub

Hi, Dnass,

use Option Explicit at the top of the prodecure, you created a new Variable with lastrow& instead fo using the filled lastrow.

Ciao,
Holger

Glad it helps you and thanks for the feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi, Dnass,

this worked perfectly

Not according to your original code where the code should look like

Sub AAA()
Dim lastrow As Long
     
With Worksheets("07DS")
  lastrow = .Range("A2").End(xlDown).Row
  If .Range("B2") = "" Then
    Exit Sub
  Else
    .Range("E2").AutoFill Destination:=.Range("E2:E" & lastrow), Type:=xlFillCopy
  End If
End With
End Sub

as the code provided will Autofill in any case.

Holger

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