VBA & Macro Problems

Use VBA to Copy Column A or B if a Cell is Blank in Excel and WPS

Steven Parker Oct 20, 2026 868 views

Question details

The user wants to populate a result column using VBA by copying values from Column A, falling back to Column B if A is blank, or leaving the result empty if both source cells are blank.

How to Copy Column A or B When a Cell is Blank Using VBA

Product: Spreadsheet
Device & OS: not provided
Scenario: Consolidating data from two columns where one or both cells might be empty, requiring conditional logic to determine which value to keep.
Observed behavior: The goal is to automate the conditional copying process row by row via a macro to produce a single consolidated result column without manual intervention.

Before you start

Ensure you have enabled macros in your spreadsheet application's trust center and made a backup copy of your workbook before running any new VBA code.

Solution 1Recommended

Use a VBA Loop with If-Else Conditions

This method loops through a specified range of rows, checks the cell values in Columns A and B, and writes the correct value to an output column.

This VBA macro uses a standard 'For...Next' loop combined with 'If...ElseIf' statements to evaluate each row individually. It ensures that the output is logically populated based on the presence of data in the source columns.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet software.

Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window where you can write your macro.

Write the Macro Code

Set up a loop (e.g., For i = 2 To 100). Inside the loop, add the logic: If Cells(i, 1).Value <> "" Then Cells(i, 3).Value = Cells(i, 1).Value ElseIf Cells(i, 2).Value <> "" Then Cells(i, 3).Value = Cells(i, 2).Value Else Cells(i, 3).Value = "" End If.

Run the Macro

Press F5 or click the 'Run' button on the toolbar to execute the code. The result column (Column C in the example) will automatically populate based on the logic.

Adjusting Column References: Remember to change the column index numbers in the code (e.g., 1 for A, 2 for B, 3 for C) and the starting row number to perfectly match your actual workbook layout.

Advanced Macros in WPS Spreadsheet

Execute VBA Macros Seamlessly in WPS Office

WPS Spreadsheet offers comprehensive built-in support for VBA macros, allowing you to run complex data consolidation tasks effortlessly. Automate conditional logic and data entry workflows just as you would in Microsoft Excel.

1. Download and Install WPS Office: Visit the official WPS website to download the free version and complete the quick installation process.
2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the data columns you need to evaluate.
3. Enable Macros and Run: Navigate to the 'Developer' tab, ensure macros are enabled, click on 'Macros', select your consolidation script, and click 'Run'.

Fully compatible with Microsoft Excel VBA macro files (.xlsm).Built-in VBA editor to write, run, and debug scripts easily.Lightweight software footprint with significantly faster loading times.A cost-effective, powerful alternative for managing advanced spreadsheet workflows.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle a situation where my output column is not adjacent to Columns A and B?

You can easily modify the column index in your VBA script. For example, if your output needs to go to Column G (the 7th column), you would use Cells(i, 7).Value as the destination in your VBA code.

Can I achieve this without VBA using standard spreadsheet formulas?

Yes. You can enter a nested formula like =IF(A2<>"", A2, IF(B2<>"", B2, "")) in your result column and drag the fill handle down. VBA is simply preferred if you want to fully automate the process and keep the output as raw text instead of formulas.

Why is my VBA macro throwing a Runtime or Out of Range error?

This typically occurs if your loop is trying to read beyond the existing rows, or if you referenced a worksheet name that doesn't exist. Ensure your loop bounds (e.g., For i = 2 To LastRow) correctly match the size of your dataset.

Excel & Data Problems