PDA

View Full Version : [SOLVED] Hide rows based on values within range



Sir Babydum GBE
09-13-2005, 03:50 AM
Hi, I need some urgent help, as I need to finish this project today cos i'm going on my vacation tomorrow - purleeeze! : pray2:

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
09-13-2005, 05:09 AM
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
09-13-2005, 06:57 AM
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
09-13-2005, 07:13 AM
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 http://vbaexpress.com/forum/images/smilies/devil.gif), until I saw that was outside the range.

Sir Babydum GBE
09-13-2005, 07:19 AM
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
09-13-2005, 07:24 AM
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.