Hide rows based on values within range

[Sir Babydum GBE]

Hi, I need some urgent help, as I need to finish this project today cos i'm going on my vacation tomorrow - purleeeze!

Range G9:AD483 has some blank cells and other cells where the text will always end in "group " and then a number (e.g. "Group 1")

C1 will contain the group I am currently examining - and will simply say "group 1" (or whatever number).

I want to run a macro that will look at what I've put in C1, and then will hide all rows in range from 9 to 483, Except rows where cells in the range G9:AD483 contain text that end with whatever group is mentioned in C1.

So for example:

Cell F28 contains "Mathematics Class A, Group 1"
Cell G29 contains "Mathematics Class A, Group 2"
And my key cell, C1, contains "group 1"

So the macro will hide row 29, but will leave row 28 alone.

Can someone help please?

[Bob Phillips]

Sub HideRows()
Dim iRow As Long
Dim oRow As Range
Dim cell As Range
Dim rng As Range
Dim sTemp As String
sTemp = Range("C1").Value
    For Each oRow In Range("G9:AD483").Rows
        iRow = 0
        For Each cell In oRow.Cells
            If cell.Value Like "*" & sTemp & "*" Then
                iRow = cell.Row
                Exit For
            End If
        Next cell
        If iRow = 0 Then
            If rng Is Nothing Then
                Set rng = Rows(oRow.Row)
            Else
                Set rng = Union(rng, Rows(oRow.Row))
            End If
        End If
    Next oRow
If Not rng Is Nothing Then
        rng.Rows.Hidden = True
    End If
End Sub

BTW, your example has the match outside of the range.

.

[Sir Babydum GBE]

Thank you so much - it's perfect!

BTW, your example has the match outside of the range.

How do you mean? (Pardon my ignorance)

[Bob Phillips]

Thank you so much - it's perfect!

You sound surprised!

How do you mean? (Pardon my ignorance)

What I mean is that you say the range is G9:AD483, but in your examples, one of the cells was F28, which wouldn't get tested. When I tested it, all rows originally got hidden, and I thought my code was wrong (I got quite traumatised, I'm not used to that ), until I saw that was outside the range.

[Sir Babydum GBE]

Oh, I see. The ranges were accurate, but I made up the F as an example and didn't spot the fact F comes before G in the alphabet. Sorry!

And no, I wasn't suprised, just breathing a sigh of relief I can finally get this finished now.

I take back everything I ever said about wessex.

Thanks.

[Bob Phillips]

Oh, I see. The ranges were accurate, but I made up the F as an example and didn't spot the fact F comes before G in the alphabet. Sorry!

That is what I suspected, which is why I didn't change it to include F.

I take back everything I ever said about wessex.

Too late! We have despatched an invasion army to Wales already.