Hide select rows based on value in a dynamic range

Hi,

I have a worksheet that has a number of header rows that should always be visible and can't be changed (Rows 1-10). This worksheet also has 3 rows at the end of the worksheet that contains summation formulas which should always be visible and can't be changed.

In between these two sections, the body of the worksheet has a number of dynamic rows that can increase or decrease as users add or delete rows.

Column C contains a drop-down list of three options. I want to have buttons that, when clicked, will hide all rows where the value in column C equals to the other two options as well as hide any blank or unused rows up to the bottom Totals line. i.e. If I click the button that says "A", only the lines from Row 11 to the bottom line that contain "A" and "All" will be shown. The Totals line will always be visible.

I've attached a sample of the workbook... hopefully it gives a better idea of what I'm trying to accomplish.

SampleBk.xlsx

Any help would be greatly appreciated!

Thanks

Thanks for the quick response!

The code hide the required cells, but:

1. It also hid the rows that were labelled "All"... these ones should always show
2. Clicking on the options hides rows correctly only if all are showing. i.e. if I click "Iron Man", then click "Hulk", all rows are hidden. Can it be done such that clicking "Iron Man" will only display "Iron Man" and "All" rows and then clicking "Hulk" would only display "Hulk" and "All" rows?

Thank you!

Try this: Change myValue to match what you need for each button

Sub MainMacro()
Dim LR As Long
Dim icell As Range
Dim myValue As String

myValue = "The Hulk" 'change this for the different buttons

Application.ScreenUpdating = False

LR = Range("C" & Rows.Count).End(xlUp).Row - 4
If LR < 11 Then Exit Sub

Range("C11:C" & LR).EntireRow.Hidden = False

For Each icell In Range("C11:C" & LR)
    If icell.Value = myValue Or icell.Value = "All" Then
    Else
        icell.EntireRow.Hidden = True
    End If
Next icell

Application.ScreenUpdating = True

End Sub

I've tested out the second book that you attached (Amended one).

Perfect, I'm seeing the "All" lines now! However, if I click the "Thor" option, there are two "All" lines that show in the bottom... can it only display the "All" line where there is a Thor in that part?

The second issue is still present: "2. Clicking on the options hides rows correctly only if all are showing. i.e. if I click "Iron Man", then click "Hulk", all rows are hidden. Can it be done such that clicking "Iron Man" will only display "Iron Man" and "All" rows and then clicking "Hulk" would only display "Hulk" and "All" rows?"

Thanks in advance! ... really appreciate your help!

Wow, that is awesome! This is exactly it... thank you so much for your help!