Quick VBA code to Hide Rows based on Cell Value

[Excel2021]

Hello,

Somebody named ZVI posted the VBA code below in 2020 which works really quickly for 1 sheet. I am wondering if this code can be altered to include multiple sheets? For my purpose just call them "Sheet 1", "Sheet 2", and "Sheet 3". Thanks.

Sub HideRows1()

Dim a()
Dim rng As Range
Dim i As Long
Dim ws As Worksheet

' Disable screen updating (CF triggering) and events triggering
Application.ScreenUpdating = False
Application.EnableEvents = False

' Main
Set ws = ActiveSheet
With ws
    .UsedRange.EntireRow.Hidden = False
    a() = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp)).Value
    ' Collect rows to be hidden in the rng
    For i = 1 To UBound(a)
        If a(i, 1) = "HIDE" Then
            If rng Is Nothing Then
                Set rng = .Rows(i)
            Else
                Set rng = Union(rng, .Rows(i))
            End If
        End If
    Next
End With

' Hide rows
If Not rng Is Nothing Then
    rng.EntireRow.Hidden = True
End If

' Restore screen uodating and events triggering
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Joe4]

See if this does what you want (I cannot really test it out without your data).

Sub HideRows1()

Dim a()
Dim rng As Range
Dim i As Long
Dim wks As Variant
Dim wk As Variant
Dim ws As Worksheet

'Disable screen updating (CF triggering) and events triggering
Application.ScreenUpdating = False
Application.EnableEvents = False

'List worksheets to include below
wks = Array("Sheet1", "Sheet2", "Sheet3")

'Loop through sheets
For Each wk In wks
    Set ws = ActiveWorkbook.Worksheets(wk)

    With ws
        .UsedRange.EntireRow.Hidden = False
        a() = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp)).Value
        ' Collect rows to be hidden in the rng
        For i = 1 To UBound(a)
            If a(i, 1) = "HIDE" Then
                If rng Is Nothing Then
                    Set rng = .Rows(i)
                Else
                    Set rng = Union(rng, .Rows(i))
                End If
            End If
        Next
    End With

'   Hide rows
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = True
    End If
    
'   Reset range variable
    Set rng = Nothing
    
Next wk

'Restore screen updating and events triggering
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Excel2021]

See if this does what you want (I cannot really test it out without your data).

Sub HideRows1()

Dim a()
Dim rng As Range
Dim i As Long
Dim wks As Variant
Dim wk As Variant
Dim ws As Worksheet

'Disable screen updating (CF triggering) and events triggering
Application.ScreenUpdating = False
Application.EnableEvents = False

'List worksheets to include below
wks = Array("Sheet1", "Sheet2", "Sheet3")

'Loop through sheets
For Each wk In wks
    Set ws = ActiveWorkbook.Worksheets(wk)

    With ws
        .UsedRange.EntireRow.Hidden = False
        a() = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp)).Value
        ' Collect rows to be hidden in the rng
        For i = 1 To UBound(a)
            If a(i, 1) = "HIDE" Then
                If rng Is Nothing Then
                    Set rng = .Rows(i)
                Else
                    Set rng = Union(rng, .Rows(i))
                End If
            End If
        Next
    End With

'   Hide rows
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = True
    End If
  
'   Reset range variable
    Set rng = Nothing
  
Next wk

'Restore screen updating and events triggering
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

See if this does what you want (I cannot really test it out without your data).

Sub HideRows1()

Dim a()
Dim rng As Range
Dim i As Long
Dim wks As Variant
Dim wk As Variant
Dim ws As Worksheet

'Disable screen updating (CF triggering) and events triggering
Application.ScreenUpdating = False
Application.EnableEvents = False

'List worksheets to include below
wks = Array("Sheet1", "Sheet2", "Sheet3")

'Loop through sheets
For Each wk In wks
    Set ws = ActiveWorkbook.Worksheets(wk)

    With ws
        .UsedRange.EntireRow.Hidden = False
        a() = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp)).Value
        ' Collect rows to be hidden in the rng
        For i = 1 To UBound(a)
            If a(i, 1) = "HIDE" Then
                If rng Is Nothing Then
                    Set rng = .Rows(i)
                Else
                    Set rng = Union(rng, .Rows(i))
                End If
            End If
        Next
    End With

'   Hide rows
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = True
    End If
  
'   Reset range variable
    Set rng = Nothing
  
Next wk

'Restore screen updating and events triggering
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

See if this does what you want (I cannot really test it out without your data).

Sub HideRows1()

Dim a()
Dim rng As Range
Dim i As Long
Dim wks As Variant
Dim wk As Variant
Dim ws As Worksheet

'Disable screen updating (CF triggering) and events triggering
Application.ScreenUpdating = False
Application.EnableEvents = False

'List worksheets to include below
wks = Array("Sheet1", "Sheet2", "Sheet3")

'Loop through sheets
For Each wk In wks
    Set ws = ActiveWorkbook.Worksheets(wk)

    With ws
        .UsedRange.EntireRow.Hidden = False
        a() = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp)).Value
        ' Collect rows to be hidden in the rng
        For i = 1 To UBound(a)
            If a(i, 1) = "HIDE" Then
                If rng Is Nothing Then
                    Set rng = .Rows(i)
                Else
                    Set rng = Union(rng, .Rows(i))
                End If
            End If
        Next
    End With

'   Hide rows
    If Not rng Is Nothing Then
        rng.EntireRow.Hidden = True
    End If
   
'   Reset range variable
    Set rng = Nothing
   
Next wk

'Restore screen updating and events triggering
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Perfect, that worked. Thanks a lot.

 

[Joe4]

You are welcome.
Glad I was able to help!