Autofill according to other column value

Question

I have some problems with VBA Autofilling. Please find my code below.

Worksheets("DataSource").Range("E" & ActiveSheet.UsedRange.Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Value = Worksheets("DataSource").Range("E" & ActiveSheet.UsedRange.Rows.Count).End(xlUp).Value + 1
ActiveCell.Offset(1, 0).Select
ActiveCell.AutoFill Destination:=Worksheets("DataSource").Range(ActiveCell.Address & ":E" & Cells(Rows.Count, "A:A").End(xlUp).Row)

Basically, I want to make this VBA as:

1) +1 from last value in Column E {ex) if E45 was "46", I want to make add E46 as "47"}

2) Select E46(47)

3) Autofill E46(47) until the row that some value exist in A.

To explain a bit more, if A has value until A89, then I want to autofill until row 89, so in this case E89.

Problem now:

Sometimes Autofilling does not work until the last row. Especially, when I run this code again it only works until the middle row.

Note: `UsedRange` has issues. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for the right way to find the last row. — BigBen, Dec 05 '19 at 16:00
Taking out all the `ActiveCell`, `ActiveSheet` and `.Select` will make for much better readability and understandability (is that a word?) too. Refer to `Range` object [correctly](https://stackoverflow.com/q/10714251/9758194) — JvdV, Dec 05 '19 at 16:05

score 1 · Answer 1 · answered Dec 05 '19 at 16:48

Think this does what you want, though you may need to change what you are actually filling the cells with.

Sub x()

Dim r As Range, n As Long

With Worksheets("DataSource")
    Set r = .Range("E" & .Rows.Count).End(xlUp) 'find the last cell in column E
    n = .Range("A" & Rows.Count).End(xlUp).Row   'find row number of last cell in A
    r.AutoFill Range(r, .Cells(n, "A"))          'autofill in E from last cell to last row as per A
End With

End Sub

Try to get out of the habit of using Select/Activate as they are usually unnecessary and inefficient.

Autofill according to other column value

1 Answers1