Excel VBA Autofill to Last Row: Step-by-Step

VBA & macros · Updated November 22, 2026

The quickest way to autofill a formula down to the last row of data in Excel VBA is to use the Range.AutoFill method combined with the End(xlDown) property to find that last row. For example, if your data is in column A and your formula is in B2, this line fills B2 down to the last row of column A: Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).FillDown.

Why Autofill in VBA?

When you record a macro and drag the fill handle, Excel records a hard-coded range like Range("B2:B10").Select and Selection.AutoFill Destination:=Range("B2:B10"). That works only for the exact sheet you recorded it on. In real work, data changes daily—rows are added or removed—so a fixed range breaks. VBA autofill to the last row makes your macro dynamic: it calculates the last row each time it runs, so it works on any dataset, no matter how many rows you have.

Finding the Last Row: The Core Technique

Before you can autofill, you must tell VBA where the data ends. The most common and reliable method is End(xlUp), which mimics pressing Ctrl+Up from the bottom of a column. Here’s the pattern:

Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

This starts at the very last cell in column A (row 1,048,576 in modern Excel) and jumps up to the last non-empty cell, returning its row number. If your data starts in row 1, this works fine. If there are blank rows in the middle, it still finds the last contiguous block from the bottom—which is usually what you want. For columns other than A, replace “A” with your column letter.

An alternative is Cells(Rows.Count, 1).End(xlUp).Row, which uses the column index (1 for A, 2 for B, etc.). Both are equivalent; use whichever reads better in your code.

Using AutoFill vs. FillDown

There are two main ways to fill a formula down in VBA: AutoFill and FillDown. Both achieve the same result, but they differ in syntax and flexibility.

  • FillDown is simpler: you define the entire range (including the cell with the formula) and call .FillDown. Example: Range("B2:B" & lastRow).FillDown. This copies the formula from the top cell of the selected range down to the bottom.
  • AutoFill is more explicit: you specify a source range and a destination range. Example: Range("B2").AutoFill Destination:=Range("B2:B" & lastRow). This is what the macro recorder generates, and it gives you control over the fill type (e.g., xlFillDefault, xlFillCopy, xlFillFormats).

For most formula-filling tasks, FillDown is faster to write and less error-prone. Use AutoFill when you need to copy formatting or use a specific fill type.

Complete Example: Filling a Formula

Suppose you have data in columns A and B, and you want to put a formula in column C that multiplies A by B. Here’s a full macro:

Sub FillFormulaToLastRow()
    Dim lastRow As Long
    ' Find last row based on column A
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    ' Put formula in C2 (assuming headers in row 1)
    Range("C2").Formula = "=A2*B2"
    
    ' Fill down to last row
    Range("C2:C" & lastRow).FillDown
End Sub

This assumes your data starts in row 1 (headers) and row 2 is the first data row. If your data starts in a different row, adjust the starting cell accordingly. Always save a copy of your workbook before running a macro—macros cannot be undone with Ctrl+Z.

Handling Blank Cells and Non-Contiguous Data

One common pitfall: if column A has blank cells in the middle, End(xlUp) from the bottom will stop at the last non-empty cell, but it might not be the true last row of your dataset if there are blanks below the last data point. For example, if A1:A5 have data, A6 is blank, and A7 has a value, End(xlUp) from the bottom returns 7, which is correct. But if A6 is blank and A7 is blank, it returns 5, which is also correct (assuming no data below). The method works from the bottom up, so it finds the last non-empty cell in that column, regardless of blanks above it. If your last row is determined by a different column (say column C has data but A doesn’t), use that column instead.

Another approach is to use UsedRange or CurrentRegion, but those can be unreliable if the sheet has formatting or stray data. The End(xlUp) method is the most direct and widely recommended.

Autofill with a Series (Numbers, Dates)

Sometimes you need to autofill a series, not a formula. For example, filling 1, 2, 3… or dates. In VBA, you can use AutoFill with the xlFillSeries type. Here’s how to fill a series of numbers from 1 to the last row:

Sub FillNumberSeries()
    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2").Value = 1
    Range("A2:A" & lastRow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
End Sub

Alternatively, you can use AutoFill with two seed values:

Range("A2:A3").Value = Array(1, 2)
Range("A2:A3").AutoFill Destination:=Range("A2:A" & lastRow), Type:=xlFillSeries

For dates, the same pattern works—put the first date in A2, the second in A3, and autofill with xlFillSeries to continue the date sequence.

Common Errors and How to Fix Them

  • “AutoFill failed” error: This usually happens when the destination range is not contiguous or the source is not a single cell. Make sure your destination is a continuous range and the source is a single cell or a range that matches the top of the destination.
  • Filling to row 1,048,576: If lastRow is 0 (because the column is empty), your range becomes B2:B0, which is invalid. Always check that lastRow > 1 before filling.
  • Formula references not updating: If you use .FillDown, Excel adjusts relative references automatically, just like dragging. If you use .Formula on a multi-cell range, it will fill the same formula without adjusting—use .FormulaR1C1 or fill one cell then fill down.

Frequently Asked Questions

How do I autofill to the last row in a specific column?

Use the same technique but reference the column that defines your last row. For example, if column C has the last data, use lastRow = Range("C" & Rows.Count).End(xlUp).Row, then fill your formula in column D from D2 to D & lastRow.

Can I autofill to the last row in a filtered list?

Yes, but you need to be careful. End(xlUp) ignores filters and looks at the actual cells. If you have filtered data, the last visible row may be different. Use Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count to get the number of visible rows, but it’s often easier to fill the entire range and let Excel skip hidden rows—though that may overwrite hidden cells. For a robust solution, loop through visible cells or use AutoFilter to identify the last visible row.

What is the difference between End(xlUp) and End(xlDown)?

End(xlUp) goes upward from a cell, stopping at the last non-empty cell before a blank. End(xlDown) goes downward, stopping at the last non-empty cell before a blank. For finding the last row of data, you typically start at the bottom of the sheet and use End(xlUp) because it’s faster and more reliable than starting at the top and using End(xlDown), which stops at the first blank cell—so it fails if there are any blanks in the data.

How do I autofill formulas without using AutoFill?

You can assign the formula to the entire range at once using Range("C2:C" & lastRow).Formula = "=A2*B2". Excel will automatically adjust relative references for each row. This is often faster than using AutoFill or FillDown.

Does autofill work in Excel for Mac?

Yes, VBA works in Excel for Mac, but there are minor differences. The Rows.Count property returns 1,048,576 on both platforms, so the code is the same. However, some keyboard shortcuts differ (e.g., Ctrl+Up becomes Command+Up), but the VBA code itself is identical.

Can I autofill to the last row in a table (ListObject)?

Yes, but you can use the table’s ListObject to get the last row more reliably. For example, Dim tbl As ListObject: Set tbl = ActiveSheet.ListObjects("Table1"), then lastRow = tbl.Range.Rows.Count. Then fill your formula in the table column by referencing the table’s data body range.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.