Range Autofill one row down only using variable preferrably

Question

I'd like to select the last row from B to Z only (that's the only row that has formulas in it.) This range should autofill only one row down and make the second last row values only (meaning remove formulas). So the last row should always have formulas in it.

a dataset that looks like this:

    A B C ..... Z
1   1 2 2 ......26
2   1 2 2 ......26
3   1 2 2 ......26
4   1 2 2 ......26
5   1 2 2 ......26

So the code should select from B5:Z5 and autofill down one row.

I have tried the following code and it gives me a runtime error.

Sub autofilllastrow()

Dim Lastrow As Integer
Dim Lastrange As Range


Lastrow = ActiveWorkbook.Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row

Range("B" & Lastrow, "Z" & Lastrow).Select

Lastrange.AutoFill Destination:=Range("B" & Lastrow + 1, "Z" & Lastrow + 1)


End Sub

How should I properly define the Destination part and make the second last row values only?

You never assign anything to Lastrange, so it stays Nothing, this will give you a runtime error — FunThomas

JvdV JvdV · Accepted Answer · 2020-01-21T07:31:58

Sub autofilllastrow()

Dim lr As Long

With ThisWorkbook.Worksheets("Sheet1")
    lr = .Cells(.Rows.Count, 2).End(xlUp).Row
    .Range(.Cells(lr + 1, 2), .Cells(lr + 1, 26)).FillDown
    .Range(.Cells(lr, 2), .Cells(lr, 26)).Value = .Range(.Cells(lr, 2), .Cells(lr, 26)).Value
End With

End Sub

Without the use of .Select
With Long data type instead of Integer
FillDown instead of AutoFill

Or if you are keen on setting a Range variable:

Sub autofilllastrow()

Dim lr As Long, rng As Range

With ThisWorkbook.Worksheets("Sheet1")
    lr = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rng = .Range(.Cells(lr, 2), .Cells(lr, 26))
    rng.Offset(1).FillDown
    rng.Value = rng.Value
End With

End Sub

Range Autofill one row down only using variable preferrably

1 Answers