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
lastRowis 0 (because the column is empty), your range becomesB2:B0, which is invalid. Always check thatlastRow > 1before filling. - Formula references not updating: If you use
.FillDown, Excel adjusts relative references automatically, just like dragging. If you use.Formulaon a multi-cell range, it will fill the same formula without adjusting—use.FormulaR1C1or 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
- Excel Power Query Web Data: How to Import & Refresh
- Excel Power Query Weighted Average: How to Calculate
- Excel Power Query Undo: What Works and What Doesn’t
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Loop Through Table Column: How-ToTo loop through a table column in Excel VBA, use a For Each loop over the ListColumn.DataBodyRange or…
- String in VBA: Meaning, Types, and Practical ExamplesIn VBA, String is the data type used to store text, such as names, messages, codes, and text…
- Create a UserForm in Excel VBA: A Step-by-Step GuideTo create a UserForm in Excel VBA, open the Visual Basic Editor, choose Insert > UserForm, add controls…
- Excel VBA: Find All Cells With a ValueTo find every cell that matches a particular value in Excel VBA, use the Range.Find method and repeat…
- Excel Macro Tiny Toggle: Make a Small On/Off ButtonIf you mean a tiny on/off control that runs a macro, Excel has no built-in feature called a…
- Excel Macro: Generate a Summary AutomaticallyYes. In Excel for Microsoft 365 on Windows, a VBA macro can read a data table, group rows…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.