VBA & Macro Problems

How to Fill Trailing Blank Cells in Excel VBA Using Another Column

Huma Ashraf Ch Oct 19, 2026 868 views

Question details

The user needs an Excel VBA macro to fill blank cells in column B with the value from the preceding row, specifically ensuring that trailing blank cells at the end of the dataset are also filled.

How to Fill Trailing Blank Cells in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Running a macro to autofill empty cells down a target column where the final rows are completely blank, causing standard row-counting methods to stop prematurely.
Observed behavior: Using End(xlUp) on column B fails to include trailing blanks because it stops at the last populated cell in that specific column. The macro requires referencing a fully populated adjacent column to determine the correct end row.

Before you start

Before running the macro, identify an adjacent column in your dataset that reliably contains data all the way down to the final row you want to process.

Solution 1Recommended

Define the Range Using an Adjacent Column and Offset

Use a fully populated column (such as Column C) to find the correct total row count, then apply the Offset property to define the range for your target column.

When trailing cells in your target column are blank, searching for the last row in that specific column will return an inaccurate result. By referencing a complete column and offsetting the range, your macro accurately captures all trailing blanks.

You can then use a standard loop through this properly defined range to check for empty cells and populate them with the value from the row above.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications editor. Go to Insert > Module to create a new script.

Set the range using an adjacent column

Declare your range variable and define it by referencing Column C's last row, offsetting by -1 to target Column B. Use code like: Set rngData = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "C").End(xlUp).Offset(0, -1))

Loop through the cells

Write a For Each loop to iterate through rngData. Add an IF statement checking if the cell is blank (If rng.Value = "" Then) and set its value to the cell immediately above it (rng.Value = rng.Offset(-1, 0).Value).

Run the macro

Press F5 or click the Run button. The macro will correctly process all cells in Column B, including the trailing blanks, up to the last row defined by Column C.

Define the Range Using an Adjacent Column and Offset

Understanding the Offset Value: The Offset(0, -1) command shifts the reference exactly zero rows down and one column to the left. If your fully populated column was Column D and you needed to target Column B, you would use Offset(0, -2).

Write and Execute VBA Macros Seamlessly in WPS Office

WPS Spreadsheet provides comprehensive built-in support for VBA macros. You can easily write, debug, and execute your automation scripts, such as filling blank cells, in a lightweight and highly compatible environment.

1. Open your workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsx file.
2. Access Developer tools: Navigate to the 'Developer' tab on the top ribbon menu.
3. Open the VBA Editor: Click 'Visual Basic' to launch the editor, then paste or write your macro code.
4. Run the macro: Click 'Run' or press F5 to instantly process your dataset and fill the trailing blank cells.

Fully compatible with Microsoft Excel VBA syntax and objectsLightweight application that executes complex macro scripts quicklyFree built-in Developer tab with an intuitive Visual Basic EditorSeamless migration of .xlsm files with zero format loss

microsoft office alternative - wps office

Frequently Asked Questions

Why does End(xlUp) fail to find the true last row?

End(xlUp) simulates pressing Ctrl + Up Arrow from the very bottom of a specific column. If the bottommost cells in that column are empty, it skips them and stops at the last cell containing data, causing trailing empty rows to be completely ignored.

Can I use SpecialCells(xlCellTypeBlanks) to fill the trailing blank cells?

Yes, you can use SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" to quickly fill blanks. However, you still need to define the overall data range correctly using an adjacent populated column so Excel knows the exact bounds of where to search for those blanks.

What if my reference column is to the left of my target column?

If your reference column (e.g., Column A) is to the left of your target column (Column B), you use a positive column offset. You would change the code to Offset(0, 1) to shift the range one column to the right.

Excel & Data Problems