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 ?

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" <ahmetaay@gmail.com> wrote in message news:d898c7aa-fc56-4e25-8f24-a99d4e2571ee@30g2000yqu.googlegroups.com...
>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.


"darkblue" <ahmetaay@gmail.com> wrote in message 
news:d898c7aa-fc56-4e25-8f24-a99d4e2571ee@30g2000yqu.googlegroups.com...
>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 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
<ahmetaay@gmail.com> 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 ?

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

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

-- 

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


"darkblue" <ahmetaay@gmail.com> wrote in message news:39797488-6dcc-4703-92bb-cdf3b4879289@a6g2000yqm.googlegroups.com...
>> 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.

On Jan 21, 6:27=A0pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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