Deleting Filtered Rows - Speeding it up

Lovelylou79

New Member
Joined
Sep 4, 2017
Messages
37
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,
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi you can add a few lines of code before and after to speed it up
Code to add before anything else
Code:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Then right before End Sub you can add
Code:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

I hope this helps
 
Upvote 0
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
 
Upvote 0
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
 
Last edited by a moderator:
Upvote 0
Hi,

another approach:

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

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

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

To delete:

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

It should be fast, please report the time.

regards
 
Last edited:
Upvote 0
@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.
 
Upvote 0
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.
Code:
Application.EnableEvents = False
Application.EnableEvents = True
If that doesn't help, let us know
 
Upvote 0
@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
 
Upvote 0
@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(y)
 
Upvote 0
There is probably a more elegant method, but this got me down to about 20 seconds on 100,000 rows.

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top