Hide Rows Based On Condition - Multiple Sheets

jwilso6 · Mar 6th 2008

Hello everyone. Let me start off by saying I'm still a beginner at VBA but I've been pretty sucessful in getting it to do what I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code. Any help?

Code

Sub Hide_Rows()


Dim i As Integer
For i = 3 To 418
        Sheets("AFA - UMBI").Select
    If ActiveSheet.Range("b" & i).Value = "2008-2" Then
        Rows(i & ":" & i).EntireRow.Hidden = True
    ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
            Rows(i & ":" & i).EntireRow.Hidden = True
        End If
Next i
End Sub

Display More

Thanks for your assistance

Joe

VBA Noob · Mar 6th 2008

Re: Condense Hide Rows Vba Code

Maybe

Code

Sub Hide_Rows()
Dim Wsht As Worksheet
Dim i As Integer
Application.ScreenUpdating = False


For Each Wsht In Worksheets
   With Wsht
        For i = 3 To 418
            Select Case .Range("b" & i).Value
                Case "2008-2", "2008-3", "2008-4", "2009-1", "2009-2", "2009-3", "2008-4"
                        .Rows(i & ":" & i).EntireRow.Hidden = True
            End Select
         Next i
    End With
Next Wsht


Application.ScreenUpdating = True
End Sub

Display More

VBA Noob

jwilso6 · Mar 6th 2008

Re: Condense Hide Rows Vba Code

It worked....much better than having to change the code for each file. Thx a bunch.

Joe

**Reafidy** · Mar 6th 2008