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,

 

[Lovelylou79]

Hi All, Thank you for all your suggestions.
Unfortunately none of these helped reduce the time this process takes.
The workbook that took 26 mins must have had something else going on with it, the file itself took 10 min to open and another 10 to enable it for editing. The workbooks this macro is run on is received from a variety of sources, so not always clean.
The code still takes 10 -11 mins to run on an ideal workbook with approx 1200 rows.
I'm still open to any other suggestions.

 

[Fluff]

Do you have any conditional formatting?

 

[igold]

You may also have some phantom bloated ranges in the book. Do you know how to check for that. How big in terms of megabytes is the workbook.

 

[Lovelylou79]

I don't know how to check for phantom bloated ranges. The size of the work book is a time sheet, the size depends on how many ppl are listed, how many shifts, and how many days of the month are covered. They can range from 300k to 2MB.

I did come up with a fix though....I have deleted this part of my code and increased "i" from 4 to 5 to read the extra row in a different section of my code...so simple. Apologies for wasting your time!