400 rows, takes ages to delete

  • Thread starter Thread starter darkblue
  • Start date Start date
D

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 ?
 
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.
 
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]
 
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
 
If you want send me the workbook private
I will look at it for you
Click to expand...

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
 
Back
Top