More efficient way of deleting rows that do not meet criteria

aurelius89

Board Regular
Joined
Mar 15, 2017
Messages
69
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:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
Hi again,

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

Take a moment to apply it to your specific conditions ...
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
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