400 rows, takes ages to delete

[darkblue]

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 ?

 

[Ron de Bruin]

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

 

[JLGWhiz]

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.

 

[Chip Pearson]

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]

 

[darkblue]

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.

 

[darkblue]

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

 

[Ron de Bruin]

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

 

[darkblue]

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