Hide rows that doesn't match cell

[Getting Fired]

Here is the example:
Kelly John Tulsa & Stillwater OK

I have a range of 10 states, 20 states, another 20 states for 3 different
people.

If state matches in the range of the 10 states, then all other rows are
hidden..thought it was as basic as,
Dim R As Range
For Each R In Range("E:E")
If R.Value = ("L:L") Then Rows(R.Row).Hidden = True
Next
End Sub

or

Dim cell As Range
'For Each cell In Range("L:L")
'If UCase(cell.Value) = "IA" Then
'cell.EntireRow.Hidden = True
'End If
'Next

I'm out of ideas.

Thanks in advance

 

[Susan]

try

Dim R As Range
For Each R In Range("E:E")
If R.Value = range("L" & r.row) Then Rows(R.Row).Hidden = True
Next
End Sub

you can't just specify (AFAIK) the whole range of L:L, you have to
tell it specifically which cell to compare it to.
if i understood you correctly.

susan

 

[Getting Fired]

Susan:

Thanks for the quick response...I have this but it just keeps looping
now...from L7:L19 I have the abbreviations to each state in the 10 state
range....

Do I have to put this code in for all 10 per cell?

I appreciate the help...what is AFAIK?

 

[Susan]

As Far As I Know AFAIK

i don't know if i'm understanding you completely, but you seem to be
saying that if the state name in column E in each row also exists in
the range of L7 - L19, then hide the row. if it doesn't exist in that
range, then keep it visible. is that right?

if so, then try this (not tested):


Dim R As Range
dim myRange as range
dim c as range

set myRange = range("L7:L19")

For Each R In Range("E:E")
For each c in MyRange
If R.Value = c.value Then Rows(R.Row).Hidden = True
next c
next r

End Sub


i think that works.

susan

 

[Getting Fired]

Your my HERO!!!!

Thank you very much!!!

As Far As I Know AFAIK

i don't know if i'm understanding you completely, but you seem to be
saying that if the state name in column E in each row also exists in
the range of L7 - L19, then hide the row. if it doesn't exist in that
range, then keep it visible. is that right?

if so, then try this (not tested):


Dim R As Range
dim myRange as range
dim c as range

set myRange = range("L7:L19")

For Each R In Range("E:E")
For each c in MyRange
If R.Value = c.value Then Rows(R.Row).Hidden = True
next c
next r

End Sub


i think that works.

susan

 

[Susan]

well, i'm glad it worked!

susan

Your my HERO!!!!

Thank you very much!!!












- Show quoted text -