Another Excel Problem.....

[paulrockliffe]

Try this one for size:

Dummy Worksheet here:

http://www.yourfilelink.com/get.php?fid=538076

I have a toggle button that scrolls through 1 to 4 levels of detail for the above sheet. I want to hide rows based on the toggle button value (linked to Cell A1) and the value in Column H.

Criteria are:

A1 = 1 Hide all rows where H doesn't = 1 or ""
A1 = 2 Hide all rows where H doesn't = 2 0r ""
A1 = 3 Hide all rows where H doesn't = 3 0r ""
A1 = 4 Hide all rows where H doesn't = 4 0r ""

I also want to hide rows where the value in Column M = 0.

I did manage to write something that I thought would work, but it got hideously stuck in a loop. I want the code to run as quickly as possible, I know if I work it out that it won't.

Column H contains numbers until it gets to the first row that doesn't need to be hidden, then column H contains "". An efficient solution would work from this cell upwards I think.

Hope you can help!

Cheers

[nickd01]

Something like this?

Sub test()

Dim rngCriteria As Range
Dim intLoop As Integer

Set rngCriteria = ActiveSheet.Range("A1")
rngCriteria.Select

For intLoop = 2 To Selection.CurrentRegion.Rows.Count
If ActiveCell.Cells(intLoop, 8) = rngCriteria.Value Then
Rows(intLoop & ":" & intLoop).Select
Selection.EntireRow.Hidden = True
End If
Next

End Sub


(Doesn't cater for your Col M criteria, and it should "unhide" all the rows first) but you get the idea...