Excel VBA Macro to Fill Columns Only Through the Last Data Row
Question details
Find the last used row with VBA, insert columns, and fill the new columns only through that row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants a macro to insert columns D through F and populate them without filling entire worksheet columns.
- Observed behavior
- The desired result is a filled range that ends at the last used row rather than extending to the bottom of the worksheet.
Open the workbook in desktop Excel, select the worksheet to modify, and make sure macros are enabled if appropriate. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain the code.
Find the last used row and fill the inserted columns
Use Find to determine the last used row, then fill columns D through F only through that row.
This macro searches the active worksheet for the last row containing a value or formula. It fills every row from row 1 through that row, including any blank rows between them.
Inserting three columns at D shifts the existing columns D and later to the right. The macro fills the newly inserted columns with the letter C.
In Excel, select Developer > Visual Basic, then choose Insert > Module.
Paste this code into the module: Sub FillThroughLastRow() Dim ws As Worksheet Dim lastCell As Range Dim lastRow As Long Dim previousScreenUpdating As Boolean Set ws = ActiveSheet 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 MsgBox "The active worksheet has no values or formulas." Exit Sub End If lastRow = lastCell.Row previousScreenUpdating = Application.ScreenUpdating On Error GoTo CleanUp Application.ScreenUpdating = False ws.Range("D1:F1").EntireColumn.Insert ws.Range("D1:F" & lastRow).Value = "C" CleanUp: Application.ScreenUpdating = previousScreenUpdating If Err.Number <> 0 Then MsgBox Err.Description End Sub
Return to Excel and select the worksheet you want to change. Choose Developer > Macros, select FillThroughLastRow, and click Run.
Inspect the new columns D through F: they should contain C from row 1 through the last used row found before insertion.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. Its format compatibility makes it easy to move everyday spreadsheet work between applications; check VBA support for your WPS edition before relying on macros.

Frequently Asked Questions
Does the macro skip blank rows within the data?
No. The range D1:F through the last used row includes intermediate blank rows. To skip them, the macro would need to test a designated data column row by row.
What happens if the worksheet is empty?
Find returns no cell, so the macro displays a message and exits without inserting columns.
Why does the filled range extend below my main table?
The macro searches the entire active worksheet. Check lower rows for values or formulas, including formulas that display an empty string.
How can I use a specific worksheet instead of the active sheet?
Replace Set ws = ActiveSheet with a qualified reference such as Set ws = ThisWorkbook.Worksheets("Sheet1"), using the actual sheet name.
How do I keep the macro when I save the workbook?
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). The standard .xlsx format does not retain VBA macros.




