Hide Excel Rows Between Non - Agented and Amendments Headings with VBA
Question details
Create a VBA macro that dynamically finds two section headings in column A and hides the rows from the Non - Agented heading through the row before Amendments.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- Section lengths change, so the user needs a macro that locates the heading rows instead of relying on fixed row numbers.
- Observed behavior
- The macro should hide the Non - Agented section and leave the Amendments heading visible.
Make sure the heading text in column A matches "Non - Agented" and "Amendments" exactly, including spaces and punctuation. Open the workbook in a macro-enabled format and enable macros before running the procedure.
Use Application.Match to find and hide the heading section
Use this macro when the Non - Agented section can move or change length and the headings are stored in column A.
Application.Match returns an error value when it cannot find a heading, so the row variables must be declared as Variant. This lets the macro safely stop if the starting heading is missing and hide through the last used row if the ending heading is unavailable.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste the following code into the module: Sub HideNonAgentedRows() Dim ws As Worksheet Dim lastRow As Long Dim startRow As Variant Dim endRow As Variant Set ws = ThisWorkbook.ActiveSheet lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row startRow = Application.Match("Non - Agented", ws.Range("A1:A" & lastRow), 0) If IsError(startRow) Then Exit Sub endRow = Application.Match("Amendments", ws.Range("A1:A" & lastRow), 0) If IsError(endRow) Then endRow = lastRow + 1 End If If endRow > startRow Then ws.Rows(startRow & ":" & (endRow - 1)).Hidden = True End If End Sub
Return to Excel, press Alt+F8, select HideNonAgentedRows, and choose Run. The Non - Agented heading and its following rows are hidden until the row before Amendments.

Add a macro to unhide the rows when needed
Use this companion macro when you need to restore hidden rows after reviewing or updating the worksheet.
Manage Dynamic Spreadsheet Sections with WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet workspace for organizing sections, hiding and unhiding rows, and working with compatible Excel files. Use its row controls to manage a section immediately, or open a macro-enabled workbook to continue spreadsheet automation workflows.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the section headings.
- 2. Locate the section rows: Find the Non - Agented heading in column A and select its row through the row immediately above Amendments.
- 3. Hide the selected rows: Right-click the selected row numbers and choose Hide to collapse the section.
- 4. Restore rows when required: Select the row numbers surrounding the hidden section, right-click, and choose Unhide.

Frequently Asked Questions
Why should the MATCH result variables be declared as Variant in VBA?
Application.Match returns an Excel error value when it cannot find a match. Declaring startRow and endRow as Variant allows IsError(startRow) and IsError(endRow) to test those results safely.
Does this macro hide the Amendments heading?
No. The code hides rows from startRow through endRow - 1, so the Non - Agented heading is included but the Amendments heading remains visible.
What happens if the Amendments heading is not found?
The macro sets endRow to lastRow + 1, then hides the rows from Non - Agented through the last used row in column A.
How can I hide rows between headings on a worksheet that is not active?
Replace Set ws = ThisWorkbook.ActiveSheet with a specific worksheet reference, such as Set ws = ThisWorkbook.Worksheets("Report"), using the actual sheet name.
How do I show the rows after running the VBA macro?
Run the UnhideAllRows procedure, or select the row numbers above and below the hidden section, right-click, and choose Unhide.




