VBA & Macro Problems

Fill Values Down to the Last Row with Excel VBA

Emma BrownEmma Brown Nov 18, 2026 868 views

Question details

Automatically copy a repeated value down a report to the last populated row.

Fill a Value Down to the Last Row with Excel VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A daily report needs a restaurant name or another value filled down column J, using column I to determine the last row.
Observed behavior
The value in J2 should be copied down column J through the last populated row in column I.
Before you start

Check that column I is populated through the last report row and that J2 contains the value to repeat. The macro will overwrite cells below J2 in column J, so check that those cells do not contain values you need to keep.

Solution 1Recommended

Use VBA to copy J2 down to the last used row in column I

Use this macro when column I reliably identifies the final row of the report.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Insert the macro

In the VBA editor, select Insert > Module and paste: Sub FillDown() Dim ws As Worksheet Dim lastRow As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row If lastRow <= 2 Then Exit Sub ws.Range("J2").AutoFill _ Destination:=ws.Range(ws.Cells(2, "J"), ws.Cells(lastRow, "J")), _ Type:=xlFillCopy End Sub

3
Run the macro

Return to the report sheet, select Developer > Macros, choose FillDown, and select Run. The macro uses the active worksheet and copies J2 through the last used row in column I.

4
Save the macro

To keep the macro for future reports, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

Use VBA to copy J2 down to the last used row in column I
Adjust the columns if needed: Change the I references to your reliable source column and the J references to your target column. If the last used row is 2 or earlier, the macro makes no changes.
Free Microsoft Office alternative

Work with daily reports in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move and edit reports. It does not fix Excel's VBA settings; check macro compatibility before relying on an existing VBA workflow.

Edit and fill repeated values in daily spreadsheet reportsOpen and save Microsoft Excel XLSX filesUse a familiar interface for easier migration from OfficeWork with a free, lightweight office suite
QA img-9

Frequently Asked Questions

How does the macro find the last row?

It starts at the bottom of column I and uses End(xlUp) to find the last nonempty cell. Use a column that is populated through the final report row.

Will the macro overwrite existing values in column J?

Yes. AutoFill copies J2 into the destination range through the last row in column I, replacing existing contents below J2.

How can I use a different source or target column?

In the VBA editor, replace the I references with the column used to find the last row. Replace the J references, including J2, with your target column and starting cell.

Why isn't the macro available after reopening the workbook?

Save the workbook through File > Save As as an Excel Macro-Enabled Workbook (*.xlsm). When reopening it, follow Excel's security prompts and enable macros only if you trust the workbook.