Deleting Filtered Rows - Speeding it up

[Lovelylou79]

Hi All,

I have the following code that filters a sheet then deletes the visible rows. The problem is, it takes a very long time to run.

'Deletes "Comments:" rows with Auto Filter
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Comments:*"
On Error Resume Next
.offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With

The "Comments' Row first appears at row 7 then every 6th row after that, ie; 7, 13, 19, 25.
Ideally I'd like the Comments rows and the row below it be deleted.

Is there a code or an alteration to my current code that can speed up the process?

Thank you in advance,

 

[Raustin]

Hi you can add a few lines of code before and after to speed it up
Code to add before anything else

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Then right before End Sub you can add

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

I hope this helps

 

[Lovelylou79]

Thanks for this. I actually have both of these in my code a little further up and further below.
i ran the above code on a sheet with around 500 lines and it took 26minutes. It’s rather frustrating

 

[Raustin]

Yeah thats wayyyy to long for just 500 lines. It should be about 1 - 3 seconds. Are you familiar with advanced filter?
I can show you a video that shows you how this work.
Advanced filter could be great because you can filter all of the data you want to keep, put it in another area of your worksheet, delete all of the original data, then replace that with the filtered data, Should take NO more than 3 seconds and probably much less.
This video on YouTube, I created shows Advanced filter techniques:
https://www.youtube.com/edit?o=U&video_id=sKOpTOTbPSI

I hope this helps.
Randy

 

[Fennek]

Hi,

another approach:

use the Range.Find to find all cells with "comments", then

rng.offset(1).formula = "=1/0"

Now in all lines for deletion there is one cell with an error.

To delete:

activesheet.usedrange.cells.specialcells(3,16).entirerow.delete

It should be fast, please report the time.

regards

 

[igold]

@Lovelylou79

Thanks for this. I actually have both of these in my code a little further up and further below.
i ran the above code on a sheet with around 500 lines and it took 26minutes. It’s rather frustrating

You must have something else going on with your computer or worksheet. I ran your code on 100,000 rows of data and it completed in just under 40 seconds.

 

[Fluff]

I agree with igold, your code should not be taking that long to run to 500 rows.
Try adding these 2 lines, the first @ the top of your code & the 2nd @ the end.

Application.EnableEvents = False
Application.EnableEvents = True

If that doesn't help, let us know

 

[igold]

@Fluff,

It could be that I am drinking too much, but I don't remember that you were a MrExcel MVP. Is that new or I am mistaken. If it is indeed new, then congratulations on being recognized as a valuable member of the forum.

Regards,

igold

 

[Fluff]

@igold
Many thanks, it is indeed new (a couple of weeks ago).
As for

It could be that I am drinking too much

If you've got room another, it's not too much

 

[igold]

There is probably a more elegant method, but this got me down to about 20 seconds on 100,000 rows.

Sub DelComments()


    Dim a
    Dim lrow As Long, i As Long
    
    Application.ScreenUpdating = False
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    a = Range("A2:A" & lrow)
    i = UBound(a)
    Do While i > 5
        If a(i, 1) Like "*Comments:*" Then
            Rows(i + 1).Delete
            i = i - 6
        Else
            i = i - 1
        End If
    Loop
    Application.ScreenUpdating = True
    
End Sub

I hope this helps.