How to Use VBA to Remove Repeated Header Rows in Excel
Question details
The user needs to automate the removal of recurring header rows throughout an Excel worksheet using VBA, ensuring the original header in row 1 remains intact.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up an exported dataset or report that contains multiple interrupting header rows.
- Observed behavior
- The dataset is broken up by repeating header rows that need to be deleted to create a continuous data range for analysis.
Identify the exact text of the recurring header in column A and ensure the Developer tab is enabled in your ribbon menu so you can access the VBA editor.
Use a Bottom-Up VBA Loop to Delete Rows
Iterating from the last row up to row 2 prevents row indexing issues that occur when deleting rows top-down.
When a macro deletes a row, Excel automatically shifts all the remaining rows upwards. If your VBA loop is moving from the top down, this shift will cause the macro to skip the row immediately following the deleted one. Looping from the bottom upwards avoids this indexing problem entirely.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor in Excel.
In the top menu, click Insert and select Module to create a blank script window.
Create a For loop that starts from the last used row in column A and counts backward to row 2. Use the syntax: For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1.
Inside the loop, add an If statement to check if the cell matches your header text. For example: If Cells(i, 1).Value = "Customer Name" Then Rows(i).EntireRow.Delete.
Press F5 or click the green Run button in the toolbar to execute the macro and clean your worksheet.

Collect and Delete Matching Cells Simultaneously
Using the Union method to group all matching header cells into a single range allows you to delete them all at once, which is significantly faster for large datasets.
Automate Data Cleanup with WPS Spreadsheet Macros
WPS Office provides robust support for VBA and macros, allowing you to seamlessly run scripts to remove repeated headers and format massive datasets without hassle.
- 1. Open Your File in WPS Spreadsheet: Download and open WPS Office, then launch your target worksheet in WPS Spreadsheet.
- 2. Access the Developer Tab: Navigate to the Developer tab located on the top ribbon menu.
- 3. Open the VBA Editor: Click on the Visual Basic icon or press Alt + F11 to open the built-in VBA editor.
- 4. Run Your Cleanup Macro: Insert a new module, paste your row-deleting macro code, and click Run to instantly remove the repeated headers.

Frequently Asked Questions
Why must I loop from the bottom upwards when deleting rows in VBA?
When you delete a row, the spreadsheet application automatically shifts all rows below it up by one index. If you loop downwards, this shift causes the macro to skip evaluating the row immediately following a deleted one.
How do I enable the Developer tab to use VBA?
Right-click anywhere on the ribbon, select Customize the Ribbon, and check the box next to Developer in the right-hand list, then click OK to confirm.
Can I adapt the macro to delete rows containing partial text matches?
Yes, you can use the Like operator with wildcards (e.g., If Cells(i, 1).Value Like "*Header*") or the InStr function to find partial text matches within the cell.
Will this macro accidentally delete my main header in row 1?
No, as long as the VBA loop is specifically instructed to stop at row 2 (using To 2 Step -1), the very first row of your worksheet is never evaluated or deleted.




