Check Box / Hide Rows with a specific keyword in a cell

Hi All,
Been awhile since I've been on here, so hopefully the community is still as vibrant as when I asked questions 8 years ago!

I have a worksheet where there is a column (Column I) with keywords meant to abbreviate another part of that row. For example, in the cells are keywords such as "PPV", "FN", or "FNP." Those keywords are there for the purpose of grouping together like items for calculating a Sumproduct bottomline.

On the top of this worksheet, I'd like to have a checkbox. Whenever I check the Box that is labeled as "PPV", I would like to hide the rows that are labeled anything but "PPV." Same for "FN." When I check that box, hide everything but "FN."

I found a video where I can hide select rows using a Macro, but this worksheet is a couple hundred rows long and I don't want to have to input each individual row to isolate just the "PPV" rows. Also, I'll be adding to this file regularly.

Any thoughts on how to best deliver this output? I am thinking it might be a function related to |=IF ("FN") Then Hide Row| if "PPV" is checked, etc.

Thank You!
Brian (BTray90)

Hi and welcome back

It would help if we could see an example workbook. Depending on your data layout it may be possible to have a very simple piece of code attached to each check box that would filter the data based on a specified rule.
Or you could loop through the rows and hide any that don't meet the criteria.

Simple enough to do (possibly) but all depends on seeing that file.

BSB

Thanks BSB and ptmuldoon for the quick replies! Attached is an example of the file in which I am trying to manipulate.

In Cell F2 is the Check Box. If that is checked, I would like for Rows 9,10,13,15,17,18,22,25,28 and 30 to either be hidden or collapsed (if I were to set up a row collapse). These are all the non-PPV rows.
Similarly for Rows between 47-69 when "PPV" is checked. This is just examples of two years, but I have about 10 years worth of data (not included).

Furthermore, at some point I would also like to be able to create something similar where I can have a check box for a "FOX" filter and hide all rows that are not "FOX" from column A.


Thank You!

It's been sometime myself with any large macro work. But you do not need to input each row to check. You can just loop your range and hide/unhide the rows as needed.

This below looks at column I (column 9) and compares to the value in cell A1 to hide it or not.

And perhaps instead of checkbox, use a dropdown selector with an onchange event.

If you can post a sample, we can help more.

Sub HideRows()

    Dim sht As Worksheet
    Dim r As Long, lrow As Long
    
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     
    Set sht = ActiveSheet
    lrow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row


    For r = lrow To 3 Step -1
        'Unhide Row
        sht.Rows(r).EntireRow.Hidden = False
        
        If Cells(r, 9).Value <> Range("A1").Value Then
        Rows(r).EntireRow.Hidden = True
        End If
    Next r
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

I know this can be done better, and could definitely cut down on the nested IF statements. But wanted to try and get you something before heading home today.

Normal module and assigning each shape to the macro. See the attached as well.

Sub HideRows()

    Dim sht As Worksheet
    Dim r As Long, lrow As Long
    
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     
    Set sht = ActiveSheet
    lrow = Range("H8").End(xlDown).Row

    'Hide all Rows
    For r = lrow To 8 Step -1
        sht.Rows(r).EntireRow.Hidden = True
        
    Next r
    
    'UnHide checked items
    For r = lrow To 8 Step -1
        'PPV
        If ActiveSheet.Shapes("Check Box 5").OLEFormat.Object.Value = 1 Then
            If Cells(r, 8).Value = "PPV" Then
                Rows(r).EntireRow.Hidden = False
            End If
        End If
        
        'FN
        If ActiveSheet.Shapes("Check Box 6").OLEFormat.Object.Value = 1 Then
            If Cells(r, 8).Value = "FN" Then
                Rows(r).EntireRow.Hidden = False
            End If
        End If
        
        'FNP
        If ActiveSheet.Shapes("Check Box 7").OLEFormat.Object.Value = 1 Then
            If Cells(r, 8).Value = "FNP" Then
                Rows(r).EntireRow.Hidden = False
            End If
        End If
        
    Next r
    

    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Really cool! Thanks ptmuldoon! How do you get Rows 47-69 to hide/unhide also when the boxes are checked/unchecked? It looks like only the Year A data is changing.