Macro That Deletes Rows Extremely Slow
 

I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub

Macro That Deletes Rows Extremely Slow
 

Could be several things, but you need to add this to the code.

Sub DynamicGet(intField As Integer, strCriterion As String)
Application.ScreenUpdating = False
application.Calculation=xlCalculationManual
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
Application.Calculation =xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"LarryP" wrote:

I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub

Macro That Deletes Rows Extremely Slow
 

Not sure why it takes so long on a particular machine (memory ?)

But the code would be more efficient if you did not select everything first

Untested changes.....

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").AutoFilter Field:=intField, Criteria1:=strCriterion
Rows("2:30000").EntireRow.Delete
Columns("AA:AD").AutoFilter Field:=intField
Range("A2").Select
End Sub



--

Regards,
Nigel




"LarryP" wrote in message
...
I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that
takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub

Macro That Deletes Rows Extremely Slow
 

Thanks Nigel (and John). I've blended your two replies into my code, and on
my confuser it runs dandy, but of course I didn't have the problem before!
I've sent the concerned user a copy to run from her Desktop and see what
happens. More news at 11. Appreciate the quick replies.

"Nigel" wrote:

Not sure why it takes so long on a particular machine (memory ?)

But the code would be more efficient if you did not select everything first

Untested changes.....

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").AutoFilter Field:=intField, Criteria1:=strCriterion
Rows("2:30000").EntireRow.Delete
Columns("AA:AD").AutoFilter Field:=intField
Range("A2").Select
End Sub



--

Regards,
Nigel




"LarryP" wrote in message
...
I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that
takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub

Macro That Deletes Rows Extremely Slow
 

Bummer! Suggestions so far didn't fix the problem. All three code versions
(original, John's, and Nigel's) work like lightning on my computer and
several others I spot-checked, but not on this one user's. She has
uninstalled and completely reinstalled Office 2003, done a cleanup and
defrag, no change. Anyone else have a thought?

"LarryP" wrote:

I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub

Macro That Deletes Rows Extremely Slow
 

Hi LarryP

You can find more code to test on this page
http://www.rondebruin.nl/delete.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message ...
Bummer! Suggestions so far didn't fix the problem. All three code versions
(original, John's, and Nigel's) work like lightning on my computer and
several others I spot-checked, but not on this one user's. She has
uninstalled and completely reinstalled Office 2003, done a cleanup and
defrag, no change. Anyone else have a thought?

"LarryP" wrote:

I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub

Macro That Deletes Rows Extremely Slow
 

Lots of info there. The Union idea is new to me and looks interesting; will
give it a go. Thanks.

"Ron de Bruin" wrote:

Hi LarryP

You can find more code to test on this page
http://www.rondebruin.nl/delete.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message ...
Bummer! Suggestions so far didn't fix the problem. All three code versions
(original, John's, and Nigel's) work like lightning on my computer and
several others I spot-checked, but not on this one user's. She has
uninstalled and completely reinstalled Office 2003, done a cleanup and
defrag, no change. Anyone else have a thought?

"LarryP" wrote:

I have an Excel macro that runs fine on many people's computers, but on one
person's machine the delete line (set off with asterisks below) takes
forever; when she gets to it, everything in the Task Manager related to
Microsoft Office (not just Excel) shows the ol' "Not Responding" message.
She has waited as long as 45 minutes before giving up on a process that takes
only a few seconds on other machines. Any ideas?

Sub DynamicGet(intField As Integer, strCriterion As String)
Columns("AA:AD").Select
Selection.AutoFilter Field:=intField, Criteria1:=strCriterion
Range("A2:BD30000").Select
**********Selection.EntireRow.Delete************
Selection.AutoFilter Field:=intField
Range("A2").Select
End Sub