Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very Slow Macro Problem
Excellent. That did the trick.
Thank you very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
slow copying problem | Excel Worksheet Functions | |||
Any new developments in the Excel 2007 slow charting problem? | Excel Discussion (Misc queries) | |||
D-sum and Grouping slow down problem | Excel Worksheet Functions | |||
Problem with Slow ReCalculation of Dynamic Range Using OFFSET | Excel Worksheet Functions | |||
Strange problem - extremely slow application. | Excel Programming |