More efficient way of deleting rows that do not meet criteria

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
    65
    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 06:08 AM.

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    1,708
    Post Thanks / Like
    Mentioned
    1 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
    65
    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 James006's Avatar
    Join Date
    Apr 2009
    Posts
    1,708
    Post Thanks / Like
    Mentioned
    1 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
    65
    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 01:54 PM.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,532
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    5 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 Part B here.

  7. #7
    Board Regular
    Join Date
    Mar 2017
    Posts
    65
    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
    30,532
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    5 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 Part B here.

  9. #9
    Board Regular
    Join Date
    Mar 2017
    Posts
    65
    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 03:47 PM.

  10. #10
    Board Regular
    Join Date
    Mar 2017
    Posts
    65
    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
  •  

 

 
DMCA.com