How to Use VBA to Fill Empty Rows with Values from Previous Row
Question details
The user needs a VBA macro script to automatically copy cell values downward into blank rows until the end of a dataset with a variable length.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Automating data entry where blank cells in specific columns (e.g., A49:B) must be populated with the value from the cell immediately above them.
- Observed behavior
- Currently, there are empty rows in the dataset that require manual copying and pasting. The user wants to dynamically locate the last used row and automatically fill all intermediate blank cells with the preceding row's data.
Before running the macro, ensure that you have enabled Developer options and macros in your spreadsheet settings, and test the VBA code on a backup copy of your worksheet.
Use a Macro to Fill Blank Cells via FormulaR1C1
This method dynamically calculates the last row, selects empty cells in the specified range, and pulls the value from the row directly above before converting the formulas into plain text.
By utilizing the FormulaR1C1 property alongside the SpecialCells method, the macro quickly applies a relative reference formula to all blank cells simultaneously. Converting them to static values immediately afterward prevents calculation errors if the data is sorted or filtered later.
Press Alt + F11 to open the Visual Basic for Applications editor. Go to 'Insert' > 'Module' to create a new module space for your script.
In your script, declare a variable to find the last used row (e.g., LastRow = Cells(Rows.Count, "A").End(xlUp).Row). Then, set your target range to apply from row 49 downward, such as Range("A49:B" & LastRow).
Use the SpecialCells(xlCellTypeBlanks) method within your code to target only the empty cells in your defined dataset.
Assign the formula '=R[-1]C' to the blank cells using the FormulaR1C1 property. Afterward, add code to copy the entire range and paste it back as xlPasteValues to remove the underlying formulas.
Close the VBA editor and press F5, or run the macro directly from the Developer tab to instantly fill in the empty rows.
Use WPS Spreadsheet for Advanced Macro Automation
WPS Office fully supports VBA macros, making it exceptionally easy to automate repetitive tasks like filling empty rows with previous values. With an intuitive Developer tab and high compatibility with Excel scripts, WPS Spreadsheet streamlines your complex data processing workflows.
- 1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the settings, and ensure the Developer tab is enabled to access advanced macro features.
- 2. Open the VBA Editor: Click on 'Developer' in the top ribbon, then click 'VBA Editor' (or press Alt + F11) to open the coding environment.
- 3. Insert and Run Code: Insert a new module, paste your dynamic row-filling script, and click Run to automatically process your dataset.
Frequently Asked Questions
Why do I get a run-time error when no blank cells are found?
If your data has no empty rows in the specified range, the SpecialCells method will throw a run-time error. You can prevent this by adding 'On Error Resume Next' before the code line that selects the blank cells.
Can I adapt this VBA macro for different columns?
Yes. You can easily modify the column letters in the VBA script. Simply change the range definition from 'A49:B' to whichever columns contain the gaps you wish to fill, such as 'C2:E'.
Will the macro overwrite my existing data in the filled rows?
No. Using the 'SpecialCells(xlCellTypeBlanks)' command ensures that only completely empty cells are targeted. Any cells in that range that already contain data or formulas will be ignored by the script.
