CODEWITHSUNDEEP
excelvba
Singlish
Singlish

Reputation: 9

Autofill according to other column value

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.

Upvotes: 1

Views: 124

Answers (1)

SJR
SJR

Reputation: 23081

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.

Upvotes: 1

Related Questions