How to Copy Multiple Excel Columns Based on Nonblank Cells Using VBA
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).

- 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.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that Developer options and Macros are enabled in your spreadsheet settings.
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.
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.
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.
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.
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.

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. Open Your Macro Workbook: Launch WPS Spreadsheets and open your .xlsm file containing the data and VBA code.
- 2. Enable Macros: Navigate to the 'Developer' tab in the ribbon and ensure that Macros are enabled for the current session.
- 3. Run the Script: Click on 'Macros', select your specific copying and reordering macro from the list, and click 'Run'.

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.




