KirnonBhale

New Member
Joined
Jul 25, 2019
Messages
11
Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub HideRowsOnSheet()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim i As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For i = 10 To 122[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    IfActiveSheet.Cells(i, "D") = 0 And ActiveSheet.Cells(i, "D")<> "" Then ActiveSheet.Rows(i).Hidden = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next i[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

The above code does the job but it takes over 2min. to finish running. This seems especially slow as I could accomplish the task that the code is performing more quickly. Is there a quicker way to accomplish the task of hiding rows with a 0 in column D?

I have a similar code for operation over 8 Tabs but it takes 20 min + to fully run which is in my mind excessive.

Any help making this run quicker would be greatly appreciated.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You could use the Autofilter to do that.
Simply record yourself applying the filter & you have the code.
 
Upvote 0
What about if you only did one test i.e. for a string of "0" rather than = 0 AND <> 0? :
Code:
Sub HideRowsOnSheet()
Dim i As Long
Application.ScreenUpdating = False
For i = 10 To 122
    IfActiveSheet.Cells(i, "D") = "0"  Then ActiveSheet.Rows(i).Hidden = True
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Maybe try this. It doesnt access the worksheet so much and does all of the hiding at once:

Code:
Sub HideRowsOnSheet2()

Application.ScreenUpdating = False

Dim fr As Long, lr As Long, arr, i As Long, rng As Range

fr = 10
lr = 122
col = 4

Range(Cells(fr, 1), Cells(lr, 1)).EntireRow.Hidden = False

arr = Range(Cells(fr, col), Cells(lr, col))

For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + fr - 1, col))
        Else
            Set rng = Cells(i + fr - 1, col)
        End If
    End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Maybe try this. It doesnt access the worksheet so much and does all of the hiding at once:

Code:
Sub HideRowsOnSheet2()

Application.ScreenUpdating = False

Dim fr As Long, lr As Long, arr, i As Long, rng As Range

fr = 10
lr = 122
col = 4

Range(Cells(fr, 1), Cells(lr, 1)).EntireRow.Hidden = False

arr = Range(Cells(fr, col), Cells(lr, col))

For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + fr - 1, col))
        Else
            Set rng = Cells(i + fr - 1, col)
        End If
    End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

Wow!!! Steve_The_Fish - that is super quick. Exactly what I needed.

Thanks for your suggestion Fluff and sykes.

sykes - the blank cells were being included in the hide when I didn't include the 2nd test.

Fluff - I don't know how to have autofilter in VBA reference the array as excluded value only rather than array included values. So i don't think that would work as well.
 
Upvote 0
Fluff - I don't know how to have autofilter in VBA reference the array as excluded value only rather than array included values. So i don't think that would work as well.
You do it like
Code:
Sub KirnonBhale()
   Range("A10:D122").AutoFilter 4, "<>0"
End Sub
 
Last edited:
Upvote 0
Maybe try this. It doesnt access the worksheet so much and does all of the hiding at once:

Code:
Sub HideRowsOnSheet2()

Application.ScreenUpdating = False

Dim fr As Long, lr As Long, arr, i As Long, rng As Range

fr = 10
lr = 122
col = 4

Range(Cells(fr, 1), Cells(lr, 1)).EntireRow.Hidden = False

arr = Range(Cells(fr, col), Cells(lr, col))

For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + fr - 1, col))
        Else
            Set rng = Cells(i + fr - 1, col)
        End If
    End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

End Sub

Just needed to say - how impressed I am by this - I wish I was able to code like this. My coding is so clunky and cumbersome in comparison.
 
Upvote 0
Its just practice. Im self taught and by using excel, reading around and picking up ideas you learn. In this case you just need to understand that accessing the worksheet and hiding rows is expensive in terms of time so this just gives you a way to do that as little as possible.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top