Excel 2003 - Named cell used as comparison for range on a different worksheet

[LolaM]

Hey folks, been trying to puzzle my way through this one but I'm tying myself up in knots trying to work it out!! A little help would be appreciated

I have a named cell (which I have called Country) on Sheet1, on cell A1. This cell contains text.

I would like to take this text value, and compare it with a range of cells on Sheet 2 (A1:A10), and if the text values do not match, hide the row.

Additionally, if Sheet2 A1:A10 contains the word 'All', I'd like the row not to be hidden.

Here's what I have so far...

Sub Country_Selection()
Dim Rng As Range, Cl As Range, i As Long


Sheets("Sheet1").Range("A1").Value = Country


Set Rng = Worksheets("Sheet2").Range("A1:A10")
i = 1


For Each Cl In Rng
If Cl.Text = "All" Then
Sheets("Sheet2").Select
Range("A" & i).EntireRow.Hidden = False
End If


If Cl.Text = rAADB Then
Sheets("Sheet2").Select
Range("A" & i).EntireRow.Hidden = False
End If

If Cl.Text <> rAADB Then
Sheets("Sheet2").Select
Range("A" & i).EntireRow.Hidden = True
End If
i = i + 1
Next Cl

End Sub

Basically what happens is the moment the macro hits this row...

"Sheets("Sheet1").Range("A1").Value = Country"

... it deletes the text from cell A1, thus there is nothing for the cells on Sheet2 to be compared with, so the only rows which are shown are those which are blank in the range on Sheet2 (which is possible). Which is perfectly logical to be honest.

What I want to change, is to stop the macro from deleting the text in Sheet1!A1, so that the comparison 'value' is always available.

Hope that makes sense!! Thanks in advance!!

 

[LolaM]

Duh, just spotted that I'd left my original values in the macro code... rAADB is my weird internal code for country (long story, don't ask!!) so where I have 'rAADB', please replace it with 'Country'!!!

Weirdly, I don't seem to be able to edit my posts???