Very Slow Macro Problem
 

Hi, I have this macro which essentially hides all rows with a value of
"0" however it takes way too long to get through its task. I think it
maybe due to hiding each row one at a time and not all at once??

Would appreciate any advice or changes to make it all happen for me
more quickly.

Regards,
Dean


Sub Macro4()
Application.ScreenUpdating = False
Set rng = Range("1:991").Rows
For i = 1 To rng.Rows.Count
If IsNumeric(rng(i).Cells(2).Value) Then
If rng(i).Cells(2).Value = 0 Then rng(i).EntireRow.Hidden =
True
End If
Next
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("1:991").Select
Selection.EntireRow.Hidden = False
Range("L3").Select
Application.ScreenUpdating = True
Application.Run Macro:="Macro6"
End Sub

Very Slow Macro Problem
 

Dean,

Try turning off the automatic calculation with
Application.Calculation = xlCalculationManual
but remember to turn it on again!
Application.Calculation = xlCalculationAutomatic

Also, try to optimise your code;
LR = Range("B65536").End(xlUp).Row ' Determine the last used row
For each cell in range("B1:B" & cstr(LR))
if cell.value = 0 then
rows(cell.row).entirerow.hidden = true
endif
next cell
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
cells.entirerow.hidden = false

Very Slow Macro Problem
 

Dean,

Your problem lies in the fact that when you set the conditions for i
(For I = 1 to rng.rows.count) you are actually setting i to go from 1
to 991 (you previously define rng as being the rows from 1 to 991).
This is why your macro is running slowly i.e. Excel is going through
991 rows regardless of whether there is data populated in these rows or
not.

A cleaner solution would be to identify which rows are populated with
data and then restrict the rng to that range. I've not posted code to
show you how to do this - the way that you could achieve this depends
on how your data is set out.

On another line of thought - you could try to filter the range and then
apply a custom filter that shows only the rows with values 0. This
would be much quicker than hiding rows - just a thought.

Another alternative would be to sort the data first and then block
delete rows where the value is 0.

There's quite a few ways you could go about this...

Brian

Dean wrote:
Hi, I have this macro which essentially hides all rows with a value of
"0" however it takes way too long to get through its task. I think it
maybe due to hiding each row one at a time and not all at once??

Would appreciate any advice or changes to make it all happen for me
more quickly.

Regards,
Dean


Sub Macro4()
Application.ScreenUpdating = False
Set rng = Range("1:991").Rows
For i = 1 To rng.Rows.Count
If IsNumeric(rng(i).Cells(2).Value) Then
If rng(i).Cells(2).Value = 0 Then rng(i).EntireRow.Hidden =
True
End If
Next
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Rows("1:991").Select
Selection.EntireRow.Hidden = False
Range("L3").Select
Application.ScreenUpdating = True
Application.Run Macro:="Macro6"
End Sub

Very Slow Macro Problem
 

Excellent. That did the trick.

Thank you very much.