Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Deleting Filtered Rows - Speeding it up

  1. #1
    New Member
    Join Date
    Sep 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting Filtered Rows - Speeding it up

    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,

  2. #2
    New Member (M)
    Join Date
    Mar 2011
    Location
    From Los Angeles
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    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

  3. #3
    New Member
    Join Date
    Sep 2017
    Posts
    32
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    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

  4. #4
    New Member (M)
    Join Date
    Mar 2011
    Location
    From Los Angeles
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    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 Joe4; Dec 6th, 2017 at 07:50 AM.

  5. #5
    Board Regular
    Join Date
    Nov 2016
    Posts
    264
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    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 by Fennek; Dec 6th, 2017 at 04:20 AM.

  6. #6
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,083
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    @Lovelylou79

    Quote Originally Posted by Lovelylou79 View Post
    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.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,150
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,083
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    @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
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    14,150
    Post Thanks / Like
    Mentioned
    259 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

    @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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,083
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting Filtered Rows - Speeding it up

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

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •