How to Use a VBA Macro to Autofill Column B to the Last Row in Column A
Question details
The user wants a VBA macro that fills column B down to the last row containing data in column A, while preserving and extending the value from the last populated cell in column B.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is automating a spreadsheet data preparation task so that a value in column B is repeated down to match the used range of column A.
- Observed behavior
- Column B currently has fewer populated rows than column A, and the user needs the last value in column B copied down to the same last row as column A.
Open the workbook in WPS Spreadsheets and press Alt+F11 to open the VBA editor. Confirm that column A contains the range that should control the fill and that column B already has at least one value to copy.
Run a VBA Macro to Fill Column B Based on Column A's Last Row
Use this solution when you need a repeatable, automated way to extend the last value in column B down to the last used row in column A without overwriting existing data.
The macro calculates the last used row in column A and the last used row in column B. If column A extends further, it defines the empty range in column B between the last B value and the last row of column A, then writes the last B value into that range.
The macro operates on the active sheet. Make sure the worksheet you want to update is active before running it, or qualify the sheet in the code.
In WPS Spreadsheets, open the workbook and press Alt+F11 to open the VBA editor.
In the VBA editor, go to Insert > Module to add a new standard module.
Copy and paste the following macro into the module window: Sub CombinedApply() Dim lastRowA As Long, lastRowB As Long Dim fillRange As Range With ActiveSheet lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row lastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row If lastRowA > lastRowB Then Set fillRange = .Range("B" & lastRowB + 1 & ":B" & lastRowA) fillRange.Value = .Cells(lastRowB, "B").Value End If End With End Sub
Click anywhere inside the macro and press F5, or go to Run > Run Sub/UserForm. The macro fills column B from the row after the last populated B cell down to the last used row in column A with the last B value.
Automate Column B Filling in WPS Spreadsheets with VBA
Run the same VBA macro inside WPS Spreadsheets to automatically fill column B from the last populated cell down to the last row in column A. WPS Spreadsheets supports VBA macros and Excel-compatible workbooks, so you can automate the task without changing your workflow.
- 1. Download and install WPS Office: Download WPS Office from the official WPS website and install WPS Spreadsheets on your computer.
- 2. Open your workbook: Launch WPS Spreadsheets and open the workbook where column B needs to be autofilled.
- 3. Open the VBA editor: Press Alt+F11 to open the VBA editor and insert a new module.
- 4. Run the fill macro: Paste and run the CombinedApply macro to fill column B down to the last row in column A.
- 5. Save the workbook: Save the workbook as a macro-enabled file if you want to keep the automation for later use.
Frequently Asked Questions
Does this macro overwrite existing values in column B?
No. It only writes to the range starting after the last populated cell in column B, so it preserves all existing values and extends the last value downward.
What happens if column A is shorter than column B?
The macro compares the last used rows and only fills column B when column A extends below column B. If column B is already as long as or longer than column A, no changes are made.
Can I run this VBA macro in WPS Spreadsheets?
Yes. Open your workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, insert a module, and run the macro just as you would in Microsoft Excel.
How do I keep the macro after saving the workbook?
Save the workbook as a macro-enabled file such as .xlsm or .xls. If you save it as a standard .xlsx workbook, the VBA project may not be retained.
