How to Insert Blank Rows Between Date Groups in WPS Spreadsheets Using VBA
Question details
The user needs a VBA macro to insert a blank separator row whenever the date value changes in a sorted worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has rows of records with a date column and wants visual separation below each date group, such as 01/05 and 02/05.
- Observed behavior
- Blank rows are not added automatically when the date changes, so the user needs an automated VBA solution.
Open the workbook in WPS Spreadsheets, confirm the data is sorted by the date column, and note the first data row number and date column letter before running the macro.
Use a Bottom-Up VBA Macro to Insert Blank Rows When the Date Changes
Use this method when your date column is sorted and you want the macro to place one blank row below each group of dates without shifting unprocessed cells.
The macro loops from the last used row upward and compares each date cell with the one above it. When the values differ, it inserts an entire row below the date group. This bottom-up approach prevents inserted rows from moving data that still needs to be checked.
Open the worksheet that contains the date-sorted data. Press Alt+F11 to open the Microsoft Visual Basic for Applications editor, or go to Developer > Visual Basic if the Developer tab is enabled.
In the VBA editor, click Insert > Module to create a new module where the macro code will be stored.
Paste the following code into the module, then set fr to the first row with data and c to the date column letter: Sub InsertRows() Const fr = 2 ' First row with data Const c = "D" ' Column containing dates Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Cells(Rows.Count, c).End(xlUp).Row For r = m To fr + 1 Step -1 If Cells(r, c).Value <> Cells(r - 1, c).Value Then Cells(r, c).EntireRow.Insert End If Next r Application.ScreenUpdating = True End Sub
Place the cursor inside the InsertRows macro and press F5, or close the editor and run Developer > Macros > InsertRows. WPS Spreadsheets inserts a blank row below each date group.
Insert Blank Rows Between Date Groups with a WPS Office VBA Macro
WPS Office Spreadsheets supports VBA macros, so you can automate the blank-row insertion directly in WPS. This method avoids manual row insertion and keeps your date groups clearly separated after sorting.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office Spreadsheets and open the workbook that contains the date-sorted rows.
- 2. Open the VBA editor: Click the Developer tab and select Visual Basic, or press Alt+F11 to open the VBA editor.
- 3. Insert and configure the macro: Go to Insert > Module, paste the InsertRows code, and set the fr constant to the first data row and c to the date column letter.
- 4. Run the macro: Press F5 inside the macro or go to Developer > Macros, select InsertRows, and click Run. WPS Spreadsheets inserts blank separator rows between date groups.
Frequently Asked Questions
Does the VBA macro require the date column to be sorted?
Yes. The macro compares each row with the previous row, so the date column should be sorted first; otherwise, blank rows may appear at inconsistent positions.
Which part of the macro code do I change for my own worksheet?
Change the fr constant to the first row that contains data and change the c constant to the letter of the column that contains the dates.
Can I run this macro in WPS Office if the workbook was created in Microsoft Excel?
Yes. WPS Office Spreadsheets can open .xlsx and .xls files, and the VBA editor can run this macro after you enable or access the macro feature.
Why does the macro loop from the bottom of the sheet upward?
Looping upward prevents the newly inserted blank rows from shifting the remaining rows that still need to be compared, so the macro checks every original data row exactly once.
How do I run the macro after adding it to a module?
In the VBA editor, click inside the InsertRows macro and press F5, or close the editor and use Developer > Macros > InsertRows > Run.
