How to Insert 25 Rows After Each Change in an Excel Column with WPS Spreadsheets VBA
Question details
The user needs a macro to automatically insert 25 blank rows after every change in a sorted key column, such as an employee number column, in an Excel workbook opened in WPS Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A sorted data set uses a key column to group records, and the user needs blank rows after each group of matching values for reports, printing, or manual data entry.
- Observed behavior
- The worksheet contains continuous rows, so no blank rows appear when the value in the key column changes.
Confirm which column contains the employee number or key value and how far the data extends. Save a backup copy of the workbook because the macro inserts physical rows and this action cannot be undone with Ctrl+Z.
Insert 25 Rows After Each Change with a Bottom-Up VBA Macro
Use this solution when the data is sorted by the key column and you want a reusable macro that inserts fixed blank rows after every group, including the final group.
Processing from the bottom upward prevents newly inserted rows from changing the positions of rows that still need to be checked.
The macro uses a variable for the key column and the number of rows, so you can adapt it quickly to other columns or group sizes.
In WPS Spreadsheets, open the target workbook, press Alt+F11 to open the VBA editor, then click Insert > Module to create a module.
In the module window, paste the following code: Sub InsertRowsAfterChange() Dim i As Long Dim lastRow As Long Dim keyColumn As Long Dim rowsToInsert As Long keyColumn = 1 rowsToInsert = 25 lastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row For i = lastRow To 2 Step -1 If Cells(i, keyColumn).Value <> Cells(i - 1, keyColumn).Value Then Rows(i).Resize(rowsToInsert).Insert Shift:=xlDown End If Next i Cells(Rows.Count, keyColumn).End(xlUp).Offset(1).Resize(rowsToInsert).EntireRow.Insert Shift:=xlDown End Sub
Set keyColumn = 1 to the column number that holds the employee number or other key value. Change rowsToInsert = 25 only if you need a different number of blank rows.
Click anywhere inside the macro and press F5, or click Run > Run Sub/UserForm. Switch back to the worksheet to review the inserted rows.
Automate Row Insertion in WPS Spreadsheets with a VBA Macro
WPS Spreadsheets includes the VBA editor, so you can run the same bottom-up macro on Excel workbooks without switching tools. Open .xlsx or .xlsm files, add the module, and insert the required blank rows after each change for clean grouped reports.
- 1. Download WPS Office: Download WPS Office from the official website, install it, and open the Excel workbook in WPS Spreadsheets.
- 2. Enable macros if prompted: When the workbook contains macros, click Enable Content in the security notice or adjust macro security in WPS Spreadsheets.
- 3. Insert and run the macro: Press Alt+F11, click Insert > Module, paste the macro code, adjust keyColumn and rowsToInsert, then press F5 to run it.
Frequently Asked Questions
Why should I process the column from the bottom upward?
Because Rows().Insert shifts later rows down. If you loop from top to bottom, the shifted rows can be re-checked or skipped, causing duplicated or missing insertions. Bottom-up processing keeps the upper positions stable.
How can I insert a different number of rows instead of 25?
Change rowsToInsert = 25 in the macro to the number of blank rows you need. The same variable is used for both the group separators and the final group, so one change updates the entire macro.
Does the macro also insert rows after the last change?
Yes. After the loop, the macro finds the last key value in the column and inserts 25 blank rows below it, so the final group also gets the required spacing.
Will this VBA macro run in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports VBA macros and can open Excel workbooks. In Excel, you may need to save the workbook as a macro-enabled .xlsm file before running the code.
What should I check before running the macro?
Confirm the data is sorted by the key column, change keyColumn = 1 to match the correct column, and save a backup copy because row insertion cannot be undone with Ctrl+Z.
