400 rows, takes ages to delete
 

I tried nearly every method I found here - autofilter, loop,
specialcells- it still takes ages to delete rows with only one
criteria.
I want to try sort first method but I don't know how to with a
criteria.
Could anyone show me an example how to use sort first then delete rows
with a criteria if possible ?

400 rows, takes ages to delete
 

Is it also slow when you use the code here
http://www.rondebruin.nl/delete.htm

--

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


"darkblue" wrote in message ...
I tried nearly every method I found here - autofilter, loop,
specialcells- it still takes ages to delete rows with only one
criteria.
I want to try sort first method but I don't know how to with a
criteria.
Could anyone show me an example how to use sort first then delete rows
with a criteria if possible ?

400 rows, takes ages to delete
 

If you have a lot of dependent formulas in your worksheet, it could be
calculating on each deletion. To prevent that, turn calculation of before
beginning the delete process and back on after the delete process. Or you
could have a Worksheet_Change macro that is being triggered with each
deletion, in which case you would probably have to disable that macro.

I don't know that sorting first would have any bearing on the two conditions
mentioned above. But if you still want help, you will probably need to post
your code that you are currently using.


"darkblue" wrote in message
...
I tried nearly every method I found here - autofilter, loop,
specialcells- it still takes ages to delete rows with only one
criteria.
I want to try sort first method but I don't know how to with a
criteria.
Could anyone show me an example how to use sort first then delete rows
with a criteria if possible ?

400 rows, takes ages to delete
 

If you have a potentially large range to delete, you can save that
range in a variable and then do one single delete operation for all
the rows, rather than deleting one row at a time. E.g,

Dim DeleteThese As Range
Dim N As Long
For N = 1 To 100
' test whether to delete. If so then
If DeleteThese Is Nothing Then
Set DeleteThese = ActiveSheet.Rows(N)
Else
Set DeleteThese = _
Application.Union(ActiveSheet.Rows(N), DeleteThese)
End If
Next N

If Not DeleteThese Is Nothing Then
DeleteThese.EntireRow.Delete
End If

In this code, the DeleteThese range variable contains all the rows
that are to be deleted and then calls Delete only once for the entire
set of rows. This is far faster than deleting the rows one by one.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Wed, 20 Jan 2010 14:03:20 -0800 (PST), darkblue
wrote:

I tried nearly every method I found here - autofilter, loop,
specialcells- it still takes ages to delete rows with only one
criteria.
I want to try sort first method but I don't know how to with a
criteria.
Could anyone show me an example how to use sort first then delete rows
with a criteria if possible ?

400 rows, takes ages to delete
 

Is it also slow when you use the code herehttp://www.rondebruin.nl/delete.htm

Yes Ron,- I am frequently there, thank you for such a helping site by
the way.

400 rows, takes ages to delete
 

Well, this is the last method i tried.

LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 11) = "Pre" Then Rows(r).Delete
Next r

To speed up i use

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

400 rows, takes ages to delete
 

If you want send me the workbook private
I will look at it for you

--

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


"darkblue" wrote in message ...
Is it also slow when you use the code herehttp://www.rondebruin.nl/delete.htm

Yes Ron,- I am frequently there, thank you for such a helping site by
the way.

400 rows, takes ages to delete
 

On Jan 21, 6:27*pm, "Ron de Bruin" wrote:
If you want send me the workbook private
I will look at it for you

Thank you Ron it is very thoughtful of you.
What I did as a last resort is this:

- copy .cells onto a newly added temp sheet
- do "delete rows with criteria" there
- copy the cleaned .cells from new sheet
- paste them onto the original sheet having cleared the old contents.
- delete the temp sheet

I simply can't understand why but now it works with lightning speed.

Thank you very much once again / regards