To fill a formula in B2 down through the last row that has data in column A, find that row with End(xlUp) and call FillDown on the destination range. The macro below does this; change the worksheet name, anchor column, and formula column to match your layout.
Quick VBA pattern
This example treats column A as the reliable indicator of which rows contain records, and assumes the formula to copy is already in B2. It fills B2 through the final row found in A, including B2 itself:
Sub FillFormulaDownToLastRow()
Dim ws As Worksheet
Dim lastRow As Long
' ThisWorkbook is the workbook containing this macro.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last nonempty cell in the data-anchor column.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Row 1 is a header; do nothing if there are no data rows.
If lastRow < 2 Then Exit Sub
' B2 must contain the formula to copy down.
If Not ws.Range("B2").HasFormula Then
MsgBox "Cell B2 does not contain a formula."
Exit Sub
End If
ws.Range("B2:B" & lastRow).FillDown
End Sub
For example, if column A has a heading in A1 and records through A150, the macro fills B2:B150. It does not decide whether those cells represent valid records; it simply uses the last nonempty cell in column A as the boundary. Before running any macro, save a copy of the workbook. Macro changes cannot be undone with Ctrl+Z.
How the last-row check works
Excel worksheets have many rows, so VBA starts at the bottom of the chosen column and moves upward to the first nonempty cell. In ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, ws.Rows.Count supplies the worksheet’s bottom row, End(xlUp) moves upward as the equivalent of pressing Ctrl+Up from the bottom, and .Row returns the row number. The same approach works in current Microsoft 365 Excel on Windows.
The column choice matters more than the exact syntax. If column A is sometimes blank for a real record, the macro may stop too early even when columns B or C contain later data. Pick a column that is populated for every record, such as an ID or date, rather than a field that is optional. A formula that returns an empty-looking string is still a formula in its cell, so it can affect where Excel finds the last occupied cell.
If the sheet has only a header in row 1, the check returns 1 and the macro exits. That guard prevents an invalid or unintended fill range. If your headers occupy more than one row, change both the start cell and the comparison to the first data row; for instance, a formula starting in row 3 needs a corresponding row-3 boundary check.
Set up and run the macro
In desktop Excel for Microsoft 365 on Windows, save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) before saving VBA code in it. Press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Replace Data with the exact worksheet tab name, and make sure B2 contains the formula you want to fill.
To run it, return to Excel, press Alt+F8, select FillFormulaDownToLastRow, and choose Run. You can also run the procedure from the Visual Basic Editor. Excel may block macros depending on the workbook’s source and your security settings; only enable macros in files you trust. The web version of Excel does not run VBA macros, so open the workbook in desktop Excel to use this procedure.
Excel for Mac supports VBA, although the editor and security prompts can differ from Windows. Option+F11 commonly opens the editor (some keyboards require Fn as well). Menu names and available macro-security controls can vary by Mac release. The worksheet and range code itself is generally the same, but macros that depend on Windows-only libraries, controls, or APIs may need changes. This example uses standard worksheet operations rather than Windows-specific features.
Choose the right way to identify the data boundary
There is no universal definition of “last row with data.” A macro must use a rule that fits the worksheet. Common choices include:
- A required column: Use
End(xlUp)in an ID, name, or other column that is filled for every record. This is simple and fast for a regular list. - Any populated column on the sheet: Search for the last cell containing a constant or formula when records may have gaps in the anchor column.
- A known table: Use the table’s data body when the range is an Excel Table and its rows are the authoritative list.
To find the last used row anywhere on a worksheet, replace the anchor-column lookup with a Find search. This version explicitly sets its search options so it does not depend on options left over from Excel’s Find dialog:
Sub FillFormulaUsingLastUsedRow()
Dim ws As Worksheet
Dim lastCell As Range
Set ws = ThisWorkbook.Worksheets("Data")
' Search formulas and constants, not formatting alone.
Set lastCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then Exit Sub
If lastCell.Row < 2 Then Exit Sub
If Not ws.Range("B2").HasFormula Then Exit Sub
ws.Range("B2:B" & lastCell.Row).FillDown
End Sub
This search finds the last row with a formula or constant anywhere on the sheet, not necessarily the last row of a particular data table. A note, title, or stray value far below the records can therefore make the fill extend farther than intended. Use the anchor-column method when the layout has a dependable key column; choose the sheet-wide search only when “any content anywhere” is genuinely the desired rule.
What FillDown copies and what it changes
Range.FillDown copies the contents and formatting of the top row of a range into the rows beneath it. In the example, B2 is the source, and B3 through the last row receive copies based on it. When B2 contains a formula with relative references, Excel adjusts those references row by row. For instance, a formula in B2 that refers to A2 will normally refer to A3 when copied to B3. Absolute references, marked with dollar signs such as $A$1, remain fixed in the usual way.
FillDown is not a “fill only empty cells” operation. If B3:B150 already contains values or formulas, they can be replaced by the copies from B2. It also copies formatting from the first row, which may be useful for consistent formula columns but surprising if rows have different formatting. Check the destination before running the macro, or adapt the code to target only the cells that should be changed.
The top cell controls the result: if B2 is blank, filling the range will propagate a blank rather than inventing a formula. The sample checks HasFormula to catch that common mistake. Remove or change the check if the source might legitimately be a constant or if you want to fill formatting or values instead. The procedure does not calculate a new formula design; it copies the existing top cell down.
Common problems and practical fixes
- The macro stops at the wrong row: Check whether the chosen anchor column has blanks near the bottom. Switch to a mandatory key column or use a carefully scoped last-used-row search.
- The macro fills too far: A stray value, note, or formula below the records may be setting the boundary. Clear unintended content or base the limit on the actual data column or table.
- The wrong sheet is used: The code names the sheet explicitly, which is safer than relying on whichever tab happens to be active. Confirm that the tab is named exactly as specified.
- The formula is overwritten or absent: Verify that the source formula is in B2 and that the destination range is safe to replace. The included formula check stops when B2 is not a formula.
- The macro cannot be run: Confirm that the file is saved as
.xlsm, that macros are permitted for this trusted workbook, and that you are using desktop Excel rather than Excel for the web.
Another source of confusion is ThisWorkbook. It means the workbook that contains the VBA code. If the macro is stored in a separate personal macro workbook or add-in, ThisWorkbook will not refer to the workbook containing the data. In that situation, deliberately set ws to the intended open workbook instead of silently relying on the active sheet; using an active workbook reference without checking it can modify the wrong file.
Tables and non-macro alternatives
If the records are an Excel Table, a calculated-column formula may be more convenient than a macro. Enter a formula in the table’s formula column and Excel generally propagates it through the table and applies it to added rows, subject to the workbook’s table and AutoCorrect settings. Tables also expand as records are added, so a separately calculated last row is often unnecessary. A table is a good fit when the data has headers and should behave as a structured list.
For a one-time operation, select the destination cells and use Excel’s Fill Down command instead of writing VBA. On Windows, select the range with the source formula at the top and use Home > Fill > Down, or press Ctrl+D. On Mac, the fill command is also available from the Home ribbon; the keyboard equivalent is commonly Control+D, though keyboard mappings can vary. Confirm the selected range carefully because the top cell is the source and lower cells are destinations.
VBA is most useful when the same task must be repeated, when a button or larger macro needs to perform it, or when the endpoint changes from run to run. For a formula that should automatically apply to new table records, a Table is often easier to maintain. For occasional manual filling, the ribbon command avoids macro security prompts and does not require a macro-enabled file.
Frequently Asked Questions
How do I fill a formula down to the last used row in Excel VBA?
Find the last row in a dependable column with Cells(Rows.Count, column).End(xlUp).Row, then apply FillDown to a range from the formula cell through that row. The article’s first macro shows a guarded example.
Does End(xlUp) find the last row on the whole worksheet?
No. It finds the last nonempty cell in the particular column used in the expression. To search across the worksheet, use a Find search or another rule that fits the sheet, and be alert to stray content below the data.
Will FillDown adjust relative formula references?
Yes. Relative references normally adjust for each destination row, while absolute references such as $A$1 stay fixed. Mixed references adjust only in the dimension that is not locked.
Can VBA fill down when there are blank rows in the data?
Yes, if the chosen anchor column still has a nonempty cell at the actual final record. Internal blanks do not stop End(xlUp) from finding the last nonempty cell, but blanks at the bottom of the anchor column can make it stop before records stored in other columns.
Can I undo a VBA fill with Ctrl+Z?
Usually not: running a VBA macro clears Excel’s normal undo history. Save a copy before running it, and verify the destination range and last-row rule before allowing the macro to change cells.
Related
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Get Month From Date: Code, Formats, and TipsIn Excel VBA, use the built-in Month function to get the month number from a date: Month(dateValue) returns…
- VBA Open Excel File and Save as CSV with Working CodeYes. In VBA, open the workbook with Workbooks.Open, then call SaveAs with FileFormat:=xlCSVUTF8 to create a UTF-8 CSV…
- Excel VBA: Find the Last Row in a RangeTo find the last populated row in a specified Excel VBA range, use the range’s Find method with…
- VBA Dictionary SumIf: How to Group and Sum Data in ExcelTo perform a SUMIF-style total in VBA, use a Scripting.Dictionary: make each category a key and add each…
- Filter an Excel PivotTable With VBA: Fields, Items, and CodeTo filter an Excel PivotTable with VBA, reference the PivotTable and field, clear any existing filter, then set…
- VBA Dictionary Count: How to Get the Number of KeysTo get the number of keys in a VBA Dictionary, read its Count property: dict.Count. It returns the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.