Excel 2002 - Slow macros

[Sanjeev S]

Hi All

I am working on a spreadsheet where if a cell doesn't
match a date range the whole row is deleted. The macro
created worked fine in Excel97. However, it can take
anything up to an hour and a half for the macro to
process now. I have tried turning of page breaks, screen
update and automatic calculations but have no luck. Is
this a bug within Excel and is there a work-around?

 

[Berend Botje]

This is indeed a bug. It has to do with the junk that is loaded into th
memory when starting the workbook. This amount can be huge, if th
workbook is old and is used frequently.

Solution:
Copy and paste the entire workbook (sheet by sheet) to anothe
workbook. Just copy the values and the VBA scripts, DO NOT cut an
paste or use the move/copy provided within excel

 

[Guest]

THanks for the quick response

This has slightly improved and helps delete around 100
rows almost instantaneously, however, it then slows down
again, is there anything else I could do?

 

[Sanjeev S]

I've tried deleting rows 1 by 1 in a new workbook and I
get the same result, basically it slows down after
deleting 20 rows 1 by 1

 

[Berend Botje]

Can you post your script here? Otherwise I cannot tell you why it doe
that...

 

[Guest]

Here is a small Example

Sub Delete_Row_600_551()

Sheets("Data").Select

If Range("G600") < Range("Q1") Then
Rows("600:600").Select
Selection.Delete Shift:=xlUp
End If

If Range("G600") > Range("S1") Then
Rows("600:600").Select
Selection.Delete Shift:=xlUp
End If


If Range("G599") < Range("Q1") Then
Rows("599:599").Select
Selection.Delete Shift:=xlUp
End If

If Range("G599") > Range("S1") Then
Rows("599:599").Select
Selection.Delete Shift:=xlUp
End If
End Sub

 

[Lester]

I'm no expert on this, but I bet your macro will speed up if you don't
use select.

Try to replace
Rows("600:600").Select
Selection.Delete Shift:=xlUp

with
Rows("600:600").Delete Shift:=xlUp

Good luck
Lester

 

[Tom Ogilvy]

Sub Delete_Row_600_551()
Dim res as Long
Dim i as Long
Sheets("Data").Select
res = Application.Calculation
application.Calculation = xlManual
Application.ScreenUpdating = False
for i = 600 to 551 step -1
If Cells(i,"G").Value< Range("Q1") or _
Cells(i,"G").Vaue > Range("S1") Then
cells(i,"G").EntireRow.Delete
End if
Next i
Application.Calculation = res
Application.ScreenUpdating = True
End Sub

 

[Sanj]

Cheers Lester, this was a great help.

That has helped a considerable amount, however, it zooms
through about 150 to 200 rows then slows again. Excel
2002 is a real pain at the moment.

Cheers

 

[Sanj]

Hi Tom

I kept getting a Debug error for some reason, I couldn't
see a fault with the macro though can you help

cheers

 

[Tom Ogilvy]

there was a typo on one line where Value was vaue. Once corrected, it
worked fine for me.

Sub Delete_Row_600_551()
Dim res As Long
Dim i As Long
Sheets("Data").Select
res = Application.Calculation
Application.Calculation = xlManual
Application.ScreenUpdating = False
For i = 600 To 551 Step -1
If Cells(i, "G").Value < Range("Q1") Or _
Cells(i, "G").Value > Range("S1") Then
Cells(i, "G").EntireRow.Delete
End If
Next i
Application.Calculation = res
Application.ScreenUpdating = True
End Sub

 

[Guest]

Hi Tom

The first time the code runs it works a treat 5 seconds
to delete the rows, however the second time it runs it
takes 25 seconds. I guess there is no getting around this.

 

[Dave Peterson]

Try adding:

Activesheet.displaypagebreaks = false
at the top, too.

When you delete a row with the pagebreaks shown, then excel wants to determine
where they go (again and again and again).