VBA Autofill Down According to Adjacent Column Using Last Cell

Question

What I am trying to do here is write a code that will autofill down in Column A to the last row of data, according to Column B, using the last cell with data in Column A as the range for autofill. For instance Column A is "Name" and B is "Date". Column B has 6 dates, while Column A has a different "Name" every few rows down. Here is what I have so far:

Sub test()

Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2").AutoFill Destination:=Range("A2:A" & lastRow)

End Sub

Here is my problem:

I would like to run the script and have it select the last filled cell in A (A4 for instance) and autofill down to the last row with data in column B.

My problem is it keeps selecting "A2" instead of the last cell in A ("A4" in this example) to autofill and fills all of column A with "A2", to the right distance none the less.

Answer 1

Sub copyDown()
Dim lastRow As Long, i&
lastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 to 2
    Cells(2,i).AutoFill Destination:=Range(Cells(2,i),Cells(lastRow,i))
next i
End Sub

As @Findwindow mentions, you don't change the cell that it autofills from. To make an easy loop (to loop through columns A and B), I like to use Cells([row],[column]) then just loop through the two.

If you don't want it to be A2 or whatever, then just change the first Cells(2,i) to where you want it to look.

Answer 2

This will find the last occupied cell in column A and use that instead:

Sub test()
Dim ws As Worksheet
Dim lastRow As Long
Dim Alastrow As Long
Set ws = ActiveSheet
lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).row
Alastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
Range("A" & Alastrow).AutoFill Destination:=Range("A" & Alastrow & ":A" & lastRow)
End Sub

Answer 3

Another easy way to achieve is as below

Sub simple_way()
    Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).Value = Range("A2").Value
End Sub