VBA & Macro Problems

How to Use VBA to Remove Repeated Header Rows in Excel

Emma BrownEmma Brown Nov 2, 2026 870 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor in Excel.

2
Insert a New Module

In the top menu, click Insert and select Module to create a blank script window.

3
Write the Bottom-Up Loop

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.

4
Add the Delete Condition

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.

5
Run the Macro

Press F5 or click the green Run button in the toolbar to execute the macro and clean your worksheet.

Use a Bottom-Up VBA Loop to Delete Rows
Preserving the Main Header: Because the loop strictly stops at row 2, your primary column headers in row 1 will remain completely untouched.
Powerful Spreadsheet Editor

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. 1. Open Your File in WPS Spreadsheet: Download and open WPS Office, then launch your target worksheet in WPS Spreadsheet.
  2. 2. Access the Developer Tab: Navigate to the Developer tab located on the top ribbon menu.
  3. 3. Open the VBA Editor: Click on the Visual Basic icon or press Alt + F11 to open the built-in VBA editor.
  4. 4. Run Your Cleanup Macro: Insert a new module, paste your row-deleting macro code, and click Run to instantly remove the repeated headers.
Highly compatible with Microsoft Excel VBA scripts (.xlsm)Efficiently process and clean up large datasetsFamiliar ribbon interface for easy macro managementFree and lightweight alternative for daily office tasks
microsoft office alternative - wps office

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.