Good evening all,
I'm looking for an efficient way to hide upwards of a thousand rows that have zero's in a single column every time a data validation cell is changed.
To elaborate, in A1 I'll have a data validation that brings the list of Red, Green, and Blue. If red is chosen, it shows row 2-20, hides rows 21-60. If Green is chosen, it shows rows 21-40, and hides rows 2-20, and 41-60. If Blue is chosen, it shows rows 41-60, and hides 2-40. (see below code)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A2:A60").EntireRow.Hidden = True
Select Case Target.Text
Case "Red": Range("A2:A20").EntireRow.Hidden = False
Case "Green": Range("A21:A40").EntireRow.Hidden = False
Case "Bue": Range("A41:A60").EntireRow.Hidden = False
End Select
End If
End Sub
With each section of rows hidden / shown, some of the rows have a 1 in column A, and others have a 0 (For example, when red is chosen and rows 2-20 are shown, A2 = 1, A3 = 1, A4-A10 = 0, A11-A20 = 1).
What I'm looking for is some way that will hide all these rows that have 0s, and only show the ones with 1s.
I've found multiple ways of doing this, though the majority take 30 minutes + to go through (my actual document has 9000 lines that this needs to happen with), and the one I've found (posted below) that works well, brings back an error when I try clearing the data validation list (which hides all rows (1-60).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icell As Range
Application.ScreenUpdating = False
If Target.Address = "$A$1" Then
Range("A2:A60").EntireRow.Hidden = True
Select Case Target.Text
Case "Red": Range("A2:A20").EntireRow.Hidden = False
Case "Green": Range("A21:A40").EntireRow.Hidden = False
Case "Blue": Range("A41:A60").EntireRow.Hidden = False
End Select
End If
For Each icell In Range("A2:A60").SpecialCells(xlCellTypeVisible)
If icell.Value = "0" Then
icell.Resize(4, 1).EntireRow.Hidden = True
End If
Next icell
Application.ScreenUpdating = True
End Sub
Bookmarks