How to Move Data Into Inserted Excel Rows with a VBA Macro in WPS Spreadsheets
Question details
The user needs a VBA macro that checks grouped data in columns G:I, J:L, and M:O, inserts rows below each record when data exists, copies the identifier from A:C, and moves the related values into the new rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet has identifier data in columns A:C and grouped three-column value blocks in G:I, J:L, and M:O. For each populated block, a new row should be inserted below the record and the block moved into columns D:F.
- Observed behavior
- The goal state is a new row below each record for every populated three-column block, with the identifier copied from A:C and the grouped values moved into D:F without disturbing the remaining column blocks.
Open the target workbook in WPS Spreadsheets and press Alt+F11 to open the Visual Basic editor. Work on a copy of the workbook because the macro inserts rows and moves cell values, and make sure the sheet containing columns A:O is active.
Create a Right-to-Left VBA Macro to Insert Rows and Move Grouped Data in WPS Spreadsheets
Use this VBA approach when you need to scan G:I, J:L, and M:O as separate three-column blocks and move each populated block into D:F without letting inserted rows shift unprocessed columns.
This macro is designed for a sheet where A:C is the identifier and the grouped data blocks are in G:I, J:L, and M:O. It checks each three-column block with COUNTA, inserts a row below the record when the block has values, copies A:C to the new row, and moves the block into D:F. Adjust the range references if your layout differs.
Open the workbook in WPS Spreadsheets, click the Developer tab, and select Visual Basic, or press Alt+F11. In the VBA editor, insert a new module by clicking Insert > Module.
Paste the following VBA subroutine into the new module: Sub MoveDataToInsertedRows() Dim ws As Worksheet Dim lastRow As Long, r As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For r = lastRow To 2 Step -1 If Application.WorksheetFunction.CountA(ws.Range("M" & r & ":O" & r)) > 0 Then ws.Rows(r + 1).Insert Shift:=xlDown ws.Range("A" & r & ":C" & r).Copy ws.Range("A" & r + 1) ws.Range("D" & r + 1 & ":F" & r + 1).Value = ws.Range("M" & r & ":O" & r).Value ws.Range("M" & r & ":O" & r).ClearContents End If Next r For r = lastRow To 2 Step -1 If Application.WorksheetFunction.CountA(ws.Range("J" & r & ":L" & r)) > 0 Then ws.Rows(r + 1).Insert Shift:=xlDown ws.Range("A" & r & ":C" & r).Copy ws.Range("A" & r + 1) ws.Range("D" & r + 1 & ":F" & r + 1).Value = ws.Range("J" & r & ":L" & r).Value ws.Range("J" & r & ":L" & r).ClearContents End If Next r For r = lastRow To 2 Step -1 If Application.WorksheetFunction.CountA(ws.Range("G" & r & ":I" & r)) > 0 Then ws.Rows(r + 1).Insert Shift:=xlDown ws.Range("A" & r & ":C" & r).Copy ws.Range("A" & r + 1) ws.Range("D" & r + 1 & ":F" & r + 1).Value = ws.Range("G" & r & ":I" & r).Value ws.Range("G" & r & ":I" & r).ClearContents End If Next r End Sub
Return to WPS Spreadsheets, press Alt+F8 to open the Macro dialog, select MoveDataToInsertedRows, and click Run. Review the inserted rows and confirm the grouped values now appear in D:F.
Run the Row-Insertion VBA Macro Directly in WPS Spreadsheets
WPS Spreadsheets supports Excel VBA macros, so you can run this row-insertion routine to copy A:C identifiers and move G:I, J:L, or M:O values into D:F while staying in a free, familiar Office environment.
- 1. Install WPS Office and open the VBA-enabled workbook: Download WPS Office Free, install it, then open your Excel macro-enabled workbook in WPS Spreadsheets.
- 2. Open the Visual Basic editor: Press Alt+F11 or go to the Developer tab and click Visual Basic to open the VBA editor.
- 3. Add and run the row-moving macro: Insert a module, paste the right-to-left VBA code, adjust the column ranges if needed, then press Alt+F8, select the macro, and click Run.
- 4. Verify the inserted rows and moved data: Check that each populated block now has its own row below the record, the identifier is copied from A:C, and the values have been moved into D:F.
Frequently Asked Questions
Does WPS Office support VBA macros that insert rows and move data?
Yes. WPS Spreadsheets includes a Visual Basic editor. Press Alt+F11 to open it, insert a module, paste the macro, and run it from the Macro dialog with Alt+F8.
Why should the three-column blocks be checked from right to left?
Checking M:O first, then J:L, then G:I means each newly inserted row appears below the current record. This prevents the insertion from shifting a block that still needs to be checked and moved.
What should I do if my grouped data is not in columns G:I, J:L, and M:O?
Update the range references in the CountA, Value, and ClearContents lines. For example, replace M:O with your rightmost block and G:I with your leftmost block, then run the macro on a copy.
Can I undo the macro after it inserts rows and moves data?
If you run the macro without saving, WPS Spreadsheets may allow Undo for the most recent action, but multiple row insertions and value moves are hard to undo reliably. Work on a copy and save only after verifying the result.
