Do until...time/power consuming

Steeviee

Active Member
Joined
Sep 9, 2009
Messages
380
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:

Code:
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! :eek: I am thinking that there should a much less intense way of doing this - maybe a filtering option?

Your thoughts please...many thanks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
It will take a while - but would suggest as a minimum you make sure you turn off screen updating (and perhaps auto calculation):

Code:
Application.ScreenUpdating = False

blah

Application.ScreenUpdating = True
 
Upvote 0
You can also check to make sure you need to toggle the visible status:

Code:
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
 
Upvote 0
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
 
Upvote 0
Pleas note missing:

Application.ScreenUpdating = True

At end of procedure...
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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