[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[_5_]]

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

--
Message posted from http://www.ExcelForum.com

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?


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

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


---
Message posted from http://www.ExcelForum.com/

.

[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


-----Original Message-----
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?


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

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


---
Message posted from http://www.ExcelForum.com/

.

.

[Berend Botje[_8_]]

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

--
Message posted from http://www.ExcelForum.com

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


-----Original Message-----
Can you post your script here? Otherwise I cannot tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.

[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

On Wed, 2 Jun 2004 08:14:55 -0700,
wrote:

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


-----Original Message-----
Can you post your script here? Otherwise I cannot tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.

[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

--
Regards,
Tom Ogilvy


wrote in message
...
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


-----Original Message-----
Can you post your script here? Otherwise I cannot tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.

[Sanj[_2_]]

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



-----Original Message-----
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

On Wed, 2 Jun 2004 08:14:55 -0700,
wrote:

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


-----Original Message-----
Can you post your script here? Otherwise I cannot tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.


.

[Sanj[_2_]]

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


-----Original Message-----
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

--
Regards,
Tom Ogilvy


wrote in message
...
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


-----Original Message-----
Can you post your script here? Otherwise I cannot tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.



.

[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


--
Regards,
Tom Ogilvy

"Sanj" wrote in message
...
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


-----Original Message-----
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

--
Regards,
Tom Ogilvy


wrote in message
...
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


-----Original Message-----
Can you post your script here? Otherwise I cannot tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.



.

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.
-----Original Message-----
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


--
Regards,
Tom Ogilvy

"Sanj" wrote in
message
...
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


-----Original Message-----
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

--
Regards,
Tom Ogilvy


wrote in message
...
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


-----Original Message-----
Can you post your script here? Otherwise I cannot
tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.



.



.

[Dave Peterson[_3_]]

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).


wrote:

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.
-----Original Message-----
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


--
Regards,
Tom Ogilvy

"Sanj" wrote in
message
...
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


-----Original Message-----
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

--
Regards,
Tom Ogilvy


wrote in message
...
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


-----Original Message-----
Can you post your script here? Otherwise I cannot
tell
you why it does
that....


---
Message posted from http://www.ExcelForum.com/

.



.



.


--

Dave Peterson