Hide Rows is cell value is 0 or "", calculations are ramping cpu usage

[DarkJester89]

Hello, I have the following code but I dont know if its the formatting of the code of there is an easy way to do this code.

It's supposed to hide rows in the range if their value is 0 or "", not unhide it. The code as is works, it just ramps up and takes about 15-20 seconds to calculate by itself.

Is there an more efficient way for this to operate? I have it a macro on a command button, not a worksheet activate or calculate at all times type code. Thank you in advance.

Sub hiderows()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A3:A73")
    If c.Value = 0 Or c.Value = "" Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Hiding the rows doesn't stop the rows calculating. It would be more efficient to filter the rows rather than loop through them one by one.

 

[DarkJester89]

It ramps when the macro is running, i didn't know if it was something about this code that's make it do that

 

[MARK858]

Try using Autofilter rather than looping through the rows.

 

[DarkJester89]

Not sure if its an option here, the calculations in the cell would already be done, would turning off calculations with manualcalc work?

Or maybe closing the loop? It's just a one time hide row, I didn't know it was looping.

 

[MARK858]

This a loop

For Each c In Range("A3:A73")

try

Range("A2:A73").AutoFilter 1, "<>0", xlAnd, "<>"

would turning off calculations with manualcalc work

It will speed it up but how much depends on whether the formulas are causing the major issue.

 

[DarkJester89]

So it worked, but now it's not letting my unhide the rows, even as a push button.

 

[MARK858]

You just turn the filter off in the Data tab.

 

[DarkJester89]

Is this macro running automatically in the back or do I need to activate it?? hmmm, it's not working, I unhide all columns/rows, and then reapply filters, but it's hiding the new changes that are not 0/"".

 

[DarkJester89]

Found it, the application screen updating was it, I removed it and it works!