VBA & Macro Problems

How to Copy Multiple Excel Columns Based on Nonblank Cells Using VBA

Chanuka GeekiyanageChanuka Geekiyanage Oct 28, 2026 870 views

Question details

The user needs to create an Excel VBA macro that copies columns A, B, and C only for rows where column A is not blank, and then pastes them into a new workbook in a specific order (C, A, B).

How to Copy Multiple Excel Columns Based on Nonblank Cells Using VBA
Product
Excel
Device & OS
not provided
Scenario
Transferring specific data columns to a new destination while filtering out blank entries and reordering the columns.
Observed behavior
The user wants to identify non-blank rows in column A, explicitly select the relevant data ranges for multiple columns, and paste them sequentially to rearrange the output.
Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that Developer options and Macros are enabled in your spreadsheet settings.

Solution 1Recommended

Sort the Data and Copy Explicit Ranges via VBA

Sort the data to move blank rows to the bottom, dynamically find the last populated row, and copy specific column ranges to reorder them during pasting.

By sorting the dataset first, all non-blank rows are grouped securely at the top. This makes it much easier to dynamically define the exact range to copy without looping through every single cell, significantly speeding up the macro execution.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor. Insert a new Module by clicking Insert > Module from the top menu.

2
Sort the Source Range

Write VBA code to apply a sort to your source data based on Column A. This action moves all empty rows to the bottom of the dataset, leaving a solid block of data at the top.

3
Identify the Last Populated Row

Determine the last populated row in Column A using code similar to `lastRow = Cells(Rows.Count, 1).End(xlUp).Row`. This gives you the exact boundary of your non-blank data.

4
Copy and Paste with Reordering

Use explicit range references to copy individual columns and paste them in the new order. For example, use `Range("C1:C" & lastRow).Copy` and paste it to the first column of the destination workbook. Then copy Column A to the second column, and Column B to the third.

Sort the Data and Copy Explicit Ranges via VBA
Use Explicit Ranges: Avoid using relative range properties like `rng.Columns(1)` if you only want to copy specific rows. Using explicit bounds like `Range("A7:A" & lastRow)` ensures only the required non-blank records are copied to the new workbook.
Advanced Macros in WPS Office

Use WPS Spreadsheets to Run Macros and Reorder Data

WPS Office fully supports VBA macros, allowing you to seamlessly execute advanced Excel scripts. You can efficiently manage, sort, and reorder your data securely within WPS Spreadsheets.

  1. 1. Open Your Macro Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the data and VBA code.
  2. 2. Enable Macros: Navigate to the 'Developer' tab in the ribbon and ensure that Macros are enabled for the current session.
  3. 3. Run the Script: Click on 'Macros', select your specific copying and reordering macro from the list, and click 'Run'.
Fully compatible with Microsoft Excel VBA macros (.xlsm and .xlsb files).Advanced data sorting and filtering capabilities without compromising speed.Free tools to automate repetitive tasks and boost your productivity.Lightweight installation with a familiar, easy-to-use interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro copying blank rows as well?

If you do not dynamically define the last populated row using a method like `End(xlUp).Row`, your macro might copy the entire column limit (e.g., 1,048,576 rows). Sorting the data first and then referencing the last valid row prevents blank cells from being included.

What is the difference between rng.Columns(1) and Range("A1:A10") in VBA?

`rng.Columns(1)` refers dynamically to the first column within an already defined specific range object, regardless of where it sits on the sheet. `Range("A1:A10")` is a fixed, absolute reference to those specific cells on the active worksheet.

Can I use Advanced Filters instead of sorting to copy non-blank cells?

Yes. Instead of altering the original sort order, you can use Excel's Advanced Filter method via VBA or set `AutoFilter` to hide blanks, then use `SpecialCells(xlCellTypeVisible).Copy` to transfer only the visible data to your new workbook.

Does WPS Office support Excel VBA macros?

Yes, WPS Office includes comprehensive VBA support, allowing you to run, edit, and create macros just as you would in Microsoft Excel, making it easy to automate complex data transfers.