Looking for Faster Hide Rows Method

[dallin01]

Currently, I have over a hundred worksheets that I first unhide all rows and then call the Hide_Rows_Script see example code below (in column 20 I sum all numbers in row if (total=0) then I hide if not I don't hide) so the key to hide or not is if total=0 in row then hide. Hope this makes sense. This works fine except it takes a few minutes to run. I would like to get it running quicker -- is there a way to select the 0 zero rows without running a loop to find them? Looking for a quicker code for this? Thanks.

Worksheet wx4 example:
a1=1 b1=1 t1(column 20) = 2 -- don't hide row
a2=0 b1=0 t1(column 20) = 0 -- hide row
a3=2 b1=2 t1(column 20) = 4 -- don't hide row


Sub hiderow()
'This macro evaluates lines to determine rows to be hidden
Sheets("wx4").Select
' Unhide rows
Rows("6:200").Select
Selection.EntireRow.Hidden = False
'Call hide rows script to hide rows, parms are for x(row) and y(column) values
Hide_Rows_Script 6, 20
end sub

Sub Hide_Rows_Script(x As Single, y As Single)
'This routine will loop through row and hide zeros
Cells(x, y).Select
Do While Cells(x, y).Value <> ""
' Hide rows
If Cells(x, y).Value = 0 Then
Rows(x).Select
Selection.EntireRow.Hidden = True
End If
' Increment to next row
x = x + 1
Loop
End Sub

 

[MrKowz]

Try this out:

Sub hiderow()
'This macro evaluates lines to determine rows to be hidden
Application.ScreenUpdating = False
Sheets("wx4").Rows("6:200").EntireRow.Hidden = False
'Call hide rows script to hide rows, parms are for x(row) and y(column) values
Hide_Rows_Script 6, 20
Application.ScreenUpdating = True
End Sub

Sub Hide_Rows_Script(x As Long, y As Long)
Dim LR      As Long, _
    rng     As Range, _
    rng1    As String
    
Application.ScreenUpdating = False
LR = Cells(Rows.Count, y).End(xlUp).Row
With Range(Cells(x, y), Cells(LR, y))
    Set rng = .Find(0, LookIn:=xlValues, lookat:=xlWhole)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.EntireRow.Hidden = True
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
Application.ScreenUpdating = True
End Sub

 

[jasonb75]

Haven't tested this version, but have used the similar before and it's significantly faster than a loop.

Sub Test()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlManual
    End With
    With Sheets("wx4").Rows("5:200")
        .EntireRow.Hidden = False
        .AutoFilter
        .AutoFilter Field:=20, Criteria1:="0", Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).EntireRow.Hidden = True
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlAutomatic
    End With
End Sub

 

[dallin01]

For some reason it doesn't find the 0.00 zeros in column? Not sure why. I determine if row is zero by a sum array formula? Hints?

example
=SUM(ABS($C6:V6),ABS(Z6))

 

[jasonb75]

Doesn't say which version you tried, for some reason my suggestion is not working without selection, I was sure it did last time, but must have been mistaken.

This works on a test sheet

Sub Test()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlManual
    End With
    Sheets("wx4").Activate
    With ActiveSheet
        With .Rows("5:200")
            .EntireRow.Hidden = False
            .AutoFilter
            .AutoFilter Field:=20, Criteria1:="0", Operator:=xlFilterValues
            .SpecialCells(xlCellTypeVisible).Select
        End With
        .AutoFilterMode = False
    End With
    Selection.EntireRow.Hidden = True
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlAutomatic
    End With
End Sub

 

[morleyuk]

I have found macros to hide slow as well particularily on the work network I'm on.....

If it's a big sheet then I tend to use a macro to run a filter to hide data.

This script will filter column 20 (T) to show anything greater than 0:

ActiveSheet.Range("T:T").AutoFilter Field:=1, Criteria1:=">0"

This will show all:

ActiveSheet.Range("T:T").AutoFilter Field:=1

 

[Smoakstack]

here is what i used (Thanks to Mr. Kowz). I am looking for zeros throughout my workbook.
Sub Hide()

Dim rng as Range
Application.Screenupdating = False
'Hides rows with 0s in Sheet 1
With Sheet1.Range("B9:B609")
Set rng = .Find("0", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Do
rng.EntireRow.Hidden = True
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With

Set rng = Nothing

'Hides columns with 0s in sheet 1
With Sheet1.Range("M611:XX611")
Set rng = .Find("0", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Do
rng.EntireColumn.Hidden = True
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With
Set rng = Nothing

Application.Screenupdating = True

End Sub

This cut my "hide" by half. I have several of these running for different sheets and different ranges.

 

[jasonb75]

I have found macros to hide slow as well particularily on the work network I'm on.....

If it's a big sheet then I tend to use a macro to run a filter to hide data.

This script will filter column 20 (T) to show anything greater than 0:

ActiveSheet.Range("T:T").AutoFilter Field:=1, Criteria1:=">0"

This will show all:

ActiveSheet.Range("T:T").AutoFilter Field:=1

Try the code I've suggested, if you look, you will see that it uses filter to keep the rows that should be hidden visible, then inverts that to give the correct selection without the annoying arrows in the headers.

A couple of bugs got in when I changed it to match the required ranges. The last was almost correct, but I realised that I forgot to recover the headers, this is now corrected.

Sub Test()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlManual
    End With
    Sheets("wx4").Activate
    With ActiveSheet
        With .Rows("5:200")
            .EntireRow.Hidden = False
            .AutoFilter
            .AutoFilter Field:=20, Criteria1:="0", Operator:=xlFilterValues
            .SpecialCells(xlCellTypeVisible).Select
        End With
        .AutoFilterMode = False
    End With
    Selection.EntireRow.Hidden = True
    Rows(5).EntireRow.Hidden = False
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlAutomatic
    End With
End Sub

 

[dallin01]

Thanks for the lesson! The final code that worked for me (Thanks Mr.Kowz) is as follows. I also needed to format my column to number - two decimals before the code would recognize the zero's.

Sub Hide_Rows()
Dim ws As Worksheet
For Each ws In Sheets(Array("qp3", "qu3", "qa3", "qetc3", "qet3", _
"clcrk3", "qgmc3", "qgm3", "rgs3", "rp3", "qsi3", "mr3"))
With ws
ws.Rows("6:200").EntireRow.Hidden = False
THide_Rows_Script 6, 10
End With
Next ws

End Sub



Sub THide_Rows_Script(x As Long, y As Long)
Dim LR As Long, _
rng As Range, _
rng1 As String

Application.ScreenUpdating = False
LR = Cells(Rows.Count, y).End(xlUp).Row
With Range(Cells(x, y), Cells(LR, y))
Set rng = .Find("0.00", LookIn:=xlValues, lookat:=xlWhole)
If Not rng Is Nothing Then
rng1 = rng.Address
Do
rng.EntireRow.Hidden = True
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With
End Sub