From LastRow to LastRow Offset VBA

user_9380923 0
Viewed 2281

I am trying to do a AutoFill from the last row to one row below. So LastRow to LastRow Offset (1).

I know how to find the last row in VBA, but some how I can't find a way to solve this puzzle.

I think It will look something like the following:

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
LastRow2 = Cells(Rows.Count, 2).End(xlUp).Offset(1)


Range("B" & LastRow : "AA" & LastRow).Select
Range("B" & LastRow2 : "AA" & LastRow2).Autofill

If something is not clear please ask, I would appreciate any help

vba excel
3 Answers

Add .Row to the end of your LastRow2 variable:

LastRow2 = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
user_108671890

Add .Row to the end of your LastRow2 variable and use the Range.AutoFill method correctly: 

SourceRange.Autofill Destination:=DestinationRange

where the DestinationRange must include the SourceRange.

I highly recommend to avoid using Select.

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

Range("B" & LastRow : "AA" & LastRow).Autofill Destination:=Range("B" & LastRow : "AA" & LastRow + 1)
                                                                        '^ Note this must be LastRow not LastRow + 1!

Alternative:

Dim LastCell As Range
Set LastCell = Cells(Rows.Count, 2).End(xlUp) 'without .Row

LastCell.Resize(1, 26).AutoFill Destination:=LastCell.Resize(2, 26) '1 down

Alternative:

Dim SourceRange As Range
Set SourceRange = Cells(Rows.Count, 2).End(xlUp).Resize(1, 26) 'results in column B to AA

SourceRange.AutoFill Destination:=SourceRange.Resize(RowSize:=2) '1 down
user_32196130

The destination range to AutoFill might be declared like this.

With ActiveSheet
    Set Rng = .Cells(.Rows.Count, "B").End(xlUp).Resize(2, 26)
End With
user_76754370
Related