Do until...time/power consuming

[Steeviee]

Hi guys,

I am having a process time issue. Basically I have a summary table that reads from the lines of various other worksheets. Many of these will be blank - but the number of blank lines will change and so I have the following code in the worksheet:

Private Sub Worksheet_Activate()
Dim x As Integer
x = 2
Do Until x = 190
If Cells(x, 5) = 0 Then
Rows(x).EntireRow.Hidden = True
Else
Rows(x).EntireRow.Hidden = False
End If
x = x + 1
Loop
End Sub

This just cycles through the 189 row, checking if the total is zero and hiding the row if it is, unhiding it if it isn't. This takes AGES! I am thinking that there should a much less intense way of doing this - maybe a filtering option?

Your thoughts please...many thanks.

 

[macleanb]

It will take a while - but would suggest as a minimum you make sure you turn off screen updating (and perhaps auto calculation):

Application.ScreenUpdating = False

blah

Application.ScreenUpdating = True

 

[macleanb]

You can also check to make sure you need to toggle the visible status:

Private Sub Worksheet_Activate()
Dim x As Integer

Application.ScreenUpdating = False
x = 2
Do Until x = 190
    If Cells(x, 5) = 0 And Rows(x).EntireRow.Hidden = False Then
        Rows(x).EntireRow.Hidden = True
    ElseIf Cells(x, 5) <> 0 And Rows(x).EntireRow.Hidden = True Then
        Rows(x).EntireRow.Hidden = False
    End If

    x = x + 1
Loop

End Sub

 

[GTO]

Not Tested. In a junk copy of your wb, try:
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()<br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Application.Calculation = xlCalculationManual<br>    <SPAN style="color:#00007F">For</SPAN> x = 190 <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>        Cells(x, 5).EntireRow.Hidden = Cells(x, 5).Value = 0<br>    <SPAN style="color:#00007F">Next</SPAN><br>    Application.Calculation = xlCalculationAutomatic<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

Mark

 

[macleanb]

Pleas note missing:

Application.ScreenUpdating = True

At end of procedure...

 

[Steeviee]

Thanks for your help both guys.

Really appreciated.

This will be a template used by many areas of the business and ow I won't have to worry about killing their PCs.

Cheers.

 

[Steeviee]

just a quick update.

From a very long time ago, I remembered doing stuff with advanced filters...this is what I came up with in the end after some recording and messing about:

Private Sub Worksheet_Activate()
    Range("A5:E234").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Rows("1:2"), Unique:=False
    
End Sub

This seems a bit faster.

Cheers
Stevie