To copy a formula in Excel VBA down to the last row of data, find the last used row in a reliable anchor column, then fill from the formula cell to that row. For example, if the formula is in D2 and column A identifies each data row, use End(xlUp) to locate the final entry in A and AutoFill to extend the formula in D.
What “last row” means in a macro
Excel worksheets contain over a million rows, so a macro should not fill a formula to the bottom of the sheet unless that is genuinely required. Usually, “last row” means the final row containing a record in a particular column: for instance, the last customer name in column A or the last transaction date in column B. That column is often called the anchor column.
The anchor matters because a worksheet can have data in some columns but not others. If column A is empty on the final record while column B contains a value, using A as the anchor stops the fill too early. Pick a column that is populated for every record you want to include. Blank cells within that column are generally fine when later rows contain data; the last nonempty cell is still found.
It is also useful to distinguish the last data row from the last formula row. A worksheet may already contain formulas below its visible records, or formatting may extend far down the sheet. The method below finds the last nonempty cell in the selected anchor column, not the last row that looks formatted.
Choose the right fill method
For a formula already entered in the first data row, AutoFill is a natural choice. It copies the formula down and adjusts relative references as Excel normally does when a formula is filled. It can also carry formatting from the source cell, so consider whether that is desirable in your sheet.
Another option is assigning a formula to the whole destination range with the FormulaR1C1 property. This is useful when you want the macro to write the formula itself instead of relying on an existing formula cell. The two approaches solve slightly different problems:
- Use
AutoFillwhen the first formula cell is already correct and you want Excel to extend it. - Use
FormulaR1C1when the macro should write the same relative formula pattern across the destination range. - Use a table calculated column when the data is already organized as an Excel table and you want formulas to extend as rows are added.
Both VBA methods below target Microsoft Excel for Microsoft 365 on Windows. The underlying VBA statements are also available in Excel for Mac, though opening the Visual Basic Editor and using keyboard shortcuts can differ.
Copy an existing formula down with AutoFill
Assume the worksheet is named Data, records begin on row 2, column A contains a value for each record, and D2 already contains the formula to extend. The following macro finds the last nonempty cell in A and fills the formula in D2 down to the corresponding row. Change the worksheet name and column references to match your workbook.
Sub CopyFormulaDown()
Dim ws As Worksheet
Dim lastRow As Long
Dim firstFormulaCell As Range
Dim fillRange As Range
' Point to the worksheet containing the records.
Set ws = ThisWorkbook.Worksheets("Data")
Set firstFormulaCell = ws.Range("D2")
' Use column A as the anchor for the final data row.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Stop if there is no data row to fill, or only the first row exists.
If lastRow < firstFormulaCell.Row Then Exit Sub
If lastRow = firstFormulaCell.Row Then Exit Sub
' Fill the formula from D2 through the matching final row.
Set fillRange = ws.Range(firstFormulaCell, ws.Cells(lastRow, firstFormulaCell.Column))
firstFormulaCell.AutoFill Destination:=fillRange, Type:=xlFillDefault
End Sub
In the code, ws.Rows.Count refers to the worksheet’s final row. Starting there and moving upward with End(xlUp) returns the row number of the last nonempty cell in column A. The destination range then runs from D2 to the cell in column D on that row. If the final record is in row 25, for example, the destination is D2:D25.
The formula must already be present in D2. The macro does not create it, and it does not verify that the cell contains a formula rather than a value. The two exit checks prevent an unnecessary fill when there are no data rows beyond the formula’s starting row. If your sheet uses a header row other than row 1, adjust the starting cell and the row assumptions accordingly.
How Excel adjusts references when filling
AutoFill follows Excel’s usual reference rules. If D2 contains a formula that refers to B2 and C2, filling it into D3 normally changes those references to B3 and C3. Relative references move with the destination cell; absolute references marked with dollar signs remain fixed. Mixed references lock only the row or column specified with a dollar sign.
That behavior is often exactly what a calculated column needs, but it can expose a mistake in the starting formula. Before running a macro on hundreds of rows, check the formula in the first row and inspect what it would become one row lower. If a lookup range or tax-rate cell should remain fixed, make sure its reference is absolute in the source formula. The macro will reproduce the reference behavior it is given; it cannot determine which references you intended to lock.
AutoFill may copy formatting along with formulas. If D2 has a special fill color or border that should not be applied to every row, use a formula assignment method or handle formatting separately. Also note that filling a destination range replaces existing contents in those cells. Do not point the macro at a range that contains values or formulas you need to preserve.
Write the formula with FormulaR1C1
Suppose B and C contain numeric values on each data row, and column D should multiply them. With the layout B2:C20 holding the inputs and D2:D20 holding the results, the R1C1 formula =RC[-2]*RC[-1] means “multiply the cell two columns to the left by the cell one column to the left.” Assigning it to the whole destination range makes Excel apply that relative pattern on every row.
Sub WriteFormulaDown()
Dim ws As Worksheet
Dim lastRow As Long
Dim formulaRange As Range
' Point to the worksheet containing the records.
Set ws = ThisWorkbook.Worksheets("Data")
' Column A identifies the records to include.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
' Write the relative multiplication formula from D2 to the last row.
Set formulaRange = ws.Range("D2:D" & lastRow)
formulaRange.FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub
For the stated layout, if B2 contains 3 and C2 contains 4, the result in D2 is 12; if B3 contains 5 and C3 contains 2, D3 is 10. The same R1C1 expression works in every row because its references are relative to the formula cell. This example is appropriate only when multiplying the two cells to the left is the intended calculation.
Assigning FormulaR1C1 to the range writes the formula into every cell in that range. It will overwrite whatever was already there. It does not copy the source cell’s formatting, so format the result range separately if needed. For formulas involving fixed cells, ranges, or more complex logic, build and test the formula carefully before assigning it.
Handle blank columns, headers, and existing formulas
The most common last-row error is using the wrong anchor column. If column A has occasional blanks or is not populated on every record, the macro can stop at an earlier row even though other columns continue. Switch the anchor to a dependable field, such as a unique record ID, or use a method that searches the relevant area when no single column is reliable.
A completely blank anchor column creates another issue: End(xlUp) returns row 1. In the sample macro, the row check exits rather than filling the entire sheet. If the actual data begins on row 5, change the guard and destination start to match that layout; otherwise a valid dataset may be mistaken for an empty one.
Cells that contain formulas returning an empty string are not truly empty to Excel’s navigation methods: the cells still contain formulas. Consequently, an anchor column populated by formulas may be treated as filled through rows whose displayed results look blank. Decide whether the last row should be based on stored formulas or on visible nonblank values, and choose the anchor accordingly.
Before filling, also decide what should happen to existing results. A fill across D2:D100 replaces the contents below D2 in that range, whether they are old formulas, manually entered values, or blanks. If those contents need review, make a backup or add checks to the macro rather than silently overwriting them.
Alternatives for finding the final row
Cells(Rows.Count, "A").End(xlUp).Row is concise and works well when you have a trustworthy anchor column. If records can be scattered across columns, another approach is to search the worksheet for the last cell containing content. That approach needs more care: search options can be remembered from previous Excel searches, so VBA code should specify its search settings rather than relying on whatever was used last.
For a consistent data block, you can also use a named range or an Excel table rather than calculate the endpoint each time. Tables are especially useful for recurring lists: when you enter a formula in a table column, Excel generally treats it as a calculated column and propagates the formula to the other table rows. New table rows can inherit that column formula as the table expands. This reduces the need for a macro just to keep a formula aligned with the records.
Tables are not always the right fit. They may change how structured references appear in formulas, and a worksheet layout with multiple separate blocks may not suit a single table. A macro with an explicit anchor column can be easier to control in those cases. Choose the simplest method that matches how the data is maintained, rather than using a more elaborate last-row search by default.
Run the macro safely
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and a fill can replace existing cell contents. Put the code in a standard module in the workbook it should operate on, then confirm the worksheet name, formula cell, anchor column, and destination column before running it.
- Save the file as an Excel Macro-Enabled Workbook (
.xlsm) if it needs to retain VBA code. - On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the macro. On a Mac, Option+F11 may open the editor; some keyboards require Fn+Option+F11, or you can use Excel’s Visual Basic Editor command from the available menus.
- Return to Excel, select the macro from the Macro dialog, and run it after checking the target sheet and ranges. You can also run a macro from the Visual Basic Editor.
Only enable macros for workbooks you trust. Avoid changing security settings to allow every macro to run. If the code is stored in a different workbook, remember that ThisWorkbook means the workbook containing the VBA code, not necessarily the workbook currently active on screen. Point ws explicitly at the intended workbook and worksheet to avoid filling the wrong file.
Frequently Asked Questions
How do I copy a formula down to the last row in Excel VBA?
Find the final row in a dependable anchor column with Cells(Rows.Count, column).End(xlUp).Row, then use AutoFill from the existing formula cell to the matching row. Check the example macro above and replace its sheet, anchor, and formula-cell references.
Can I fill the formula down without selecting cells?
Yes. The examples refer directly to worksheet ranges and do not use Select or Activate. Direct references are generally clearer and avoid depending on which sheet or cell happens to be active.
Why does my macro stop before the last data row?
Most often, the chosen anchor column is blank on the final record, so Excel finds an earlier last cell in that column. Use a column populated for every record, or use a search strategy suited to data spread across multiple columns.
Will AutoFill change relative cell references?
Yes. Relative references normally adjust for each destination row, while absolute references stay fixed. Check the first formula and the reference types before filling, especially when the formula points to a fixed rate, lookup range, or settings cell.
Can I use this method with Excel for the web?
Excel for the web can open macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for VBA; a table formula or another non-VBA approach may suit a workbook that must be updated in the browser.
Related
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
- Excel Shortcut to Select the Rest of a Column Down
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox New Line: Add Line Breaks to PromptsTo put a new line in a VBA InputBox prompt, join the prompt text with vbCrLf or vbNewLine.…
- Excel VBA Message Box: How to Use MsgBox in MacrosIn Excel VBA, use the MsgBox function to show a message, optionally display buttons or an icon, and…
- Add Strings to a Dictionary in VBA: Step-by-StepTo add strings to a VBA Dictionary, create a Dictionary object and call its Add method with a…
- Excel VBA Enum: How It Works and When to Use ItAn Excel VBA Enum is a named set of numeric constants: it lets code use readable labels such…
- Excel VBA Advanced Filter: Using Multiple CriteriaTo use multiple criteria with Excel VBA Advanced Filter, put conditions that must all be true on the…
- Excel VBA: Find a Column Number by Header Name in a RowTo find a column by its header in Excel VBA, search the header row with Range.Find and read…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.