Forum Discussion

BriceChapman's avatar
BriceChapman
Copper Contributor
Mar 06, 2024

Search a column for 2 rows with keywords and hide what's in between them

Hi everyone,

 

I am trying to hide a section of rows from my excel sheet, the issue being the location of this section is dynamic. Below is an example of my sheet

 

Agented
aaa
Non - Agented
aaa
Amendments
aaa

 

What I'm trying to do is hide the "Non - Agented" section, so that only the "Agented" and "Amendments" sections are visible. I tried to write a loop until function to loop until it found the words "Non - Agented" and "Amendments" in column A, but I've been having a hard time finding a way to select the range that's in between those keywords to hide it.

 

For context, this table is dynamic and the # of rows in each section changes from day to day. Any help or advice is greatly appreciated, thank you!

excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 06, 2024

    BriceChapman 

    Sub hide()

Dim h, i, j, k As Long

h = Range("A" & Rows.Count).End(xlUp).Row
i = Application.WorksheetFunction.Match("Non - Agented", Range("A:A"), 0)
j = Application.WorksheetFunction.Match("Amendments", Range("A:A"), 0)

For k = 1 To h
If k >= i And k < j Then
Rows(k).Hidden = True

Else
End If
Next k

End Sub

    In the attached file you can run these lines of code to hide the rows between the keywords if the data is in column A. There is a macro to unhide the range as well.

  • djclements's avatar
    djclements
    Bronze Contributor
    Mar 17, 2024

    BriceChapman No need to loop here... just use the Match function to return the row number where "Non - Agented" and "Amendments" are found, then set the Hidden property for the range of Rows to True.

     

    If your daily table always contains sections for "Agented", "Non - Agented" and "Amendments" (in that order), the following code should work:

     

    Sub HideNonAgentedRows()

    Dim ws As Worksheet, rg As Range, i As Long, j As Long
    Set ws = ThisWorkbook.ActiveSheet
    Set rg = ws.Columns(1)
    i = WorksheetFunction.Match("Non - Agented", rg, 0)
    j = WorksheetFunction.Match("Amendments", rg, 0) - 1

    ws.Rows(i & ":" & j).Hidden = True

End Sub

     

    However, it's important to note that the WorksheetFunction.Match method will throw a run-time error if either "Non - Agented" or "Amendments" is not found within the range. If this is a possibility from one day to the next, consider using the Application.Match method instead, which will return an error value if no match is found and can be handled as follows:

     

    Sub HideNonAgentedRows()

    Dim ws As Worksheet, lastRow As Long, rg As Range, i As Variant
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set rg = ws.Range("A1:A" & lastRow)
    i = Application.Match("Non - Agented", rg, 0)
    If IsError(i) Then Exit Sub

    Dim j As Variant
    j = Application.Match("Amendments", rg, 0)
    If IsError(j) Then
        j = lastRow
    Else
        j = j - 1
    End If

    ws.Rows(i & ":" & j).Hidden = True

End Sub

     

    Note: the key to making this method work is to declare the i and j variables as type Variant instead of Long, so they can accept either numeric or error values.

Resources