unhiding and hiding rows

[Paul_of_Abingdon]

I would like to see only those rows which value matches with master cell. for
example, if C5=P, I want to see only those rows which has value of P or C.
Similary, if the value C5=L, then I want to see only those rows which has
value of L and C. Category C is common rows. So, I need to see them no matter
what is in C5. Thanks in advance for your help.

 

[Gord Dibben]

How will C5 get changed from P to L?

What column or columns would the P's C's and L's be located?

This macro will hide all rows then unhide according to value in C5.

I used column D as the column in which the letters will be found.

Tweak as necessary.

Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long
ActiveSheet.Rows.Hidden = True
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Range("C5").Value = "L" And _
Cells(RowNdx, "D").Value = "L" Or _
Cells(RowNdx, "D").Value = "C" Then
Rows(RowNdx).Hidden = False
ElseIf Range("C5").Value = "P" And _
Cells(RowNdx, "D").Value = "P" Or _
Cells(RowNdx, "D").Value = "C" Then
Rows(RowNdx).Hidden = False
End If
Next RowNdx
End Sub


Gord Dibben MS Excel MVP

 

[Paul_of_Abingdon]

I have modified the spread sheet. It looks like this:
1. Top few rows are header.
2. C5 has drop down list to choose the category. Three selections, P, L and X.
3. Column D is used to mark category. Some rows in beginning and some rows
at the end are common rows which are applicable for all category. So, they
don't have any marking. Only rows in the middle section (i.e., from D41 to
D140) of the spreadsheet have marking of P, L or X. What I really want is an
option to be able to hide rows in the middle which does not match the value
in C5. In another word, if C5 value is changed to P, I want to hide all rows
which are marked L or X, automatically. Similary, if C5 is changed to L, I
wan to hide all rows with P and X marking. But if the C5 value is blank, I
would like to see all rows.