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

Thread: More efficient way of deleting rows that do not meet criteria

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default More efficient way of deleting rows that do not meet criteria

    I have some code that deletes rows that are not in a specified list of row numbers that are to be kept. It functions exactly as intended.

    Code:
      For lRow = numRowsInBBS To 1 Step -1
        
        lMatch = 0
        On Error Resume Next
        lMatch = Application.Match(lRow, ws.Range("AE4:AE" & numRows).Value, 0&)
        On Error GoTo 0
        
        If Not CBool(lMatch) Then
          wsImport.Cells(lRow, 1).EntireRow.Delete
        End If
      Next
    End Sub
    However, this takes a monumental amount of time. To do this on 150 rows takes a couple of minutes of processing. I have documents that could be 1000s of rows long.

    Is there a better way of achieving my goal here?

    Essentially I want to delete all rows on a specified sheet EXCEPT for the row numbers specified in AE4:AE?? (This is calculated by numRows) on a different sheet.
    Last edited by aurelius89; Jun 19th, 2017 at 05:08 AM.

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Hello,

    The best method ... is by far ... using Autofilter ...

    see link Delete Rows Meeting Condition/Criteria. Delete Rows in Excel

    HTH

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Hi,

    My data is not going to be in a contiguous range unfortunately.

    For example, I may want to keep rows 5,6,7,43,77,104,105,110 (Which would be specified in AE4:AE?? on another sheet)
    All other rows = Deleted

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Hi again,

    The VBA Autofilter method will exactly perform as you need it ...

    Take a moment to apply it to your specific conditions ...

  5. #5
    Board Regular
    Join Date
    Mar 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Trying to get the autofilter way to work...but no success

    I am trying to:
    Set the data in the range "AE4:AE??" as the data for an array
    Then use ZZ as a helper column containing row numbers
    Then filter out the rows I want to keep
    Then delete all visible rows
    Then show the rows that were filtered

    When I run it, it filters everything out, which tells me "rowsToKeep" is empty, but the range "AE4:AE??" on the spcified sheet does contain values. Where am I going wrong?


    Code:
    Dim rowsToKeep() As Variant: rowsToKeep = ws.Range("AE4:AE" & numRows)
    Dim allRows As Range: Set allRows = Range("ZZ1:ZZ" & numRowsInBBS)
    
    With wsImport
    .Range(allRows.Address).Formula = "=row()"
    .Range(allRows.Address).AutoFilter Field:=1, Criteria1:=rowsToKeep, Operator:=xlFilterValues
    .Range(allRows.Address).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .Range(allRows.Address).AutoFilter Field:=1
    End With
    Last edited by aurelius89; Jun 19th, 2017 at 12:54 PM.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    29,070
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Quote Originally Posted by aurelius89 View Post
    Code:
      For lRow = numRowsInBBS To 1 Step -1
        
        lMatch = 0
        On Error Resume Next
        lMatch = Application.Match(lRow, ws.Range("AE4:AE" & numRows).Value, 0&)
        On Error GoTo 0
        
        If Not CBool(lMatch) Then
          wsImport.Cells(lRow, 1).EntireRow.Delete
        End If
      Next
    End Sub
    I think I got your variable names applied correctly. I believe the snippet of code you posted above can be replaced with this code snippet and I also believe it will run noticeably quicker...
    Code:
    UnusedCol = wsImport.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    RowsToKeep = " " & Join(Application.Transpose(ws.Range("AE4", ws.Cells(Rows.Count, "AE").End(xlUp)))) & " "
    With wsImport.Cells(1, UnusedCol).Resize(numRowsInBBS)
      .Value = Evaluate(Replace("IF(ISNUMBER(FIND("" ""&ROW(@)&"" "",""" & RowsToKeep & """)),"""",""X"")", "@", .Address))
      .SpecialCells(xlConstants).EntireRow.Delete
    End With
    If you declare your variables, here is the declaration statement for the two variables I added...
    Code:
    Dim UnusedCol As Long, RowsToKeep As String
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  7. #7
    Board Regular
    Join Date
    Mar 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Thanks,
    It ended up deleting every row though unfortunately.
    Why might this be?

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    29,070
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Quote Originally Posted by aurelius89 View Post
    Thanks,
    It ended up deleting every row though unfortunately.
    Why might this be?
    Does you list in AE4:AE## have any blank cells within it?

    Did you remove the loop code that you posted in Message #1?

    Just so you know, the code worked correctly for the test sheets I set up based on how I interpreted what you described. Is there any chance you can post a copy of your workbook online (maybe via DropBox) so that we can see your exact layout and test our code directly on it?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  9. #9
    Board Regular
    Join Date
    Mar 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Not blank, but could contain 0. E.g - 0,0,0,4,5,6,0,0,0,0,0,12,13,14

    Yes (well it is commented out for now)


    I have uploaded it here:
    https://ufile.io/dxcy7

    For a little idea of what's going on:
    The code is at the bottom of the "TrimBBSRows" module.
    To test, on the "Form Controls" tab, press the "import, export and unload"
    Choose "Send data....." to begin the process, which will also call "TrimBBSRows" among other things
    The sheet "Imported Schedule" is the sheet that needs the rows deleting, based on the values in "Weights" AE4:AE??, which will not appear until they are called (Before the trimming but after you press "Send data..")

    Once the process has finished, you will have to reopen the sheet to reset as you wont be able to import new schedules.


    Yes it is messy, I am not a VBA programmer, merely picking it up as I go.
    Last edited by aurelius89; Jun 19th, 2017 at 02:47 PM.

  10. #10
    Board Regular
    Join Date
    Mar 2017
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: More efficient way of deleting rows that do not meet criteria

    Any success with this?

User Tag List

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com