How to Create a VBA Macro to Copy Nonblank Rows into an Excel Table
Question details
The user needs a VBA macro to selectively copy rows from a specific range into an existing Excel table only if a certain column is not blank, while appending a specific cell's value to an extra column in that table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data consolidation where qualifying rows (non-blank in column E) from a source sheet need to be appended to a master table on a different sheet.
- Observed behavior
- Requires a programmatic VBA script to loop through rows, check for empty cells, dynamically add a ListRow to a ListObject table, populate it with data, and manage screen updating for performance.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and the Developer tab is visible. Verify the exact names of your source worksheet, destination worksheet, and target table before modifying the code.
Write a VBA Macro Using ListRows and For Loops
Use a VBA loop to iterate through the source rows, check for blanks, and dynamically add new rows to the destination ListObject table.
In VBA, tables are treated as ListObjects. To add a new row to an existing table, you use the ListRows.Add method. By looping through the target range and checking if the cell is empty using IsEmpty or checking for a null string, you can filter out blank rows.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications Editor. Click 'Insert' in the top menu and select 'Module' to create a new blank script window.
Start your macro by defining the Sub. Add 'Application.ScreenUpdating = False' to prevent the screen from flashing. Create a For loop that iterates from 7 to 12 (e.g., 'For i = 7 To 12').
Inside the loop, write an IF statement to check column E: 'If Trim(Worksheets("Calc").Cells(i, "E").Value) <> "" Then'. This ensures only rows with data in column E are processed.
Within the IF block, declare a new row object: 'Set newRow = Worksheets("MyTotal").ListObjects("TblTest").ListRows.Add'. Assign the values from columns E-I to the first 5 columns of the new row, and assign the value of Worksheets("Calc").Range("F16") to the 6th column.
Close your IF block and Next loop. Finish the macro by adding 'Application.ScreenUpdating = True' to turn the screen refresh back on.
Automate Data Workflows with WPS Spreadsheet Macros
WPS Office provides robust support for VBA and macros, allowing you to easily automate repetitive tasks like copying nonblank rows into tables.
- 1. Install WPS Office: Download and install WPS Office on your computer.
- 2. Enable the Developer Tab: Open your spreadsheet, go to the top ribbon, and access the Developer tab to find macro tools.
- 3. Write and Run Code: Click 'Visual Basic', insert your module, paste the code to copy nonblank rows, and click 'Run'.
Frequently Asked Questions
Why am I getting a 'Subscript out of range' error when running this macro?
This error occurs when the VBA code cannot find the specified worksheet or table. Check your workbook to ensure the sheet names exactly match 'Calc' and 'MyTotal', and the table is actually named 'TblTest' without any leading or trailing spaces.
How do I run this macro automatically by clicking a button?
Go to the Developer tab, click on 'Insert', and choose the 'Button' form control. Draw the button anywhere on your sheet. An 'Assign Macro' dialog box will appear; select your newly created macro from the list and click OK.
Can I use Microsoft Excel VBA code in WPS Office?
Yes, WPS Office Pro and advanced editions offer extensive support for Microsoft Excel VBA. Most standard VBA scripts, including ListObject manipulations, will run seamlessly in the WPS Spreadsheet environment without modification.
