VBA & Macro Problems

Hide Excel Rows Between Non - Agented and Amendments Headings with VBA

Anum MushtaqAnum Mushtaq Nov 12, 2026 869 views

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.

Hide Excel Rows Between Two Headings with a VBA Macro
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Visual Basic Editor

In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.

2
Paste the hide macro

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

3
Run the macro

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.

Use Application.Match to find and hide the heading section
Heading matching is exact: If a heading contains extra spaces or different punctuation, update the text inside Application.Match so it matches the worksheet value exactly.
Work with spreadsheet macros in WPS Office

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. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the section headings.
  2. 2. Locate the section rows: Find the Non - Agented heading in column A and select its row through the row immediately above Amendments.
  3. 3. Hide the selected rows: Right-click the selected row numbers and choose Hide to collapse the section.
  4. 4. Restore rows when required: Select the row numbers surrounding the hidden section, right-click, and choose Unhide.
Hide or unhide selected worksheet rows from the row context menu.Keep changing report sections organized without fixed row references.Open and edit Microsoft Excel .xlsx workbooks with strong format compatibility.Supports common Excel workbook formats, including .xls and .xlsm files.Familiar spreadsheet layout helps you move from Microsoft Excel quickly.
microsoft office alternative - wps office

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.