CODEWITHSUNDEEP
excelvba
Peter
Peter

Reputation: 65

LastRow to new LastRow VBA

I'm trying to create a macro that does a AutoFill from the LastRow to the new LastRow.

I have a dataset which contains formulas from column A to AA. When I add a new ID number (in column D) I have to do a Autofill manually.

I have used the following code in the past to do a AutoFill from the LastRow to one row below:

Dim sourcerange As Range
Set sourcerange = Cells(Rows.Count, 2).End(xlUp).Resize(1, 26)
sourcerange.AutoFill Destination:=sourcerange.Resize(rowsize:=2)

However it can acure that I add multiple ID numbers. Therefor i need a macro that goes from the LastRow (which can be detected in column A) to the (new) LastRow Offset (1) in column D

Upvotes: 0

Views: 54

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

Just determine the NewLastRow in column D and calculate the RowSize:=NewLastRow - SourceRange.Row + 1

Dim SourceRange As Range
Set SourceRange = Cells(Rows.Count, 2).End(xlUp).Resize(1, 26)

Dim NewLastRow As Long 'get new last row in column D
NewLastRow = Cells(Rows.Count, "D").End(xlUp).Row

SourceRange.AutoFill Destination:=SourceRange.Resize(RowSize:=NewLastRow - SourceRange.Row + 1)

Please note that you say "which can be detected in column A" but …

Set SourceRange = Cells(Rows.Count, 2).End(xlUp)

… detects on column 2 which is column B!
If you want the detection on column A instead of B you must use …

Set SourceRange = Cells(Rows.Count, "A").End(xlUp).Offset(ColumnOffset:=1).Resize(1, 26)

Upvotes: 1

Related Questions