More efficient way of deleting rows that do not meet criteria

[aurelius89]

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.

  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.

 

[Rick Rothstein]

Any success with this?

You have a lot going on in that workbook and I don't have the time right now to digest it all. One thing you might try though... if I understood what I saw correctly, it looks like Column AC (that column designation is from memory, but I think it is correct) contains the same numbers as Column AE but without all those 0's and with no gaps between the numbers. That is what the numbers looked like in the test sheet I made up originally, so try changing any references in my code from Column AE to Column AC and see if that makes it work for you.

 

[James006]

Hello,

If I am not mistaken ... solution was already provided at Ozgrid ...

Delete rows that are NOT in a given list of row numbers

 

[Rick Rothstein]

aurelius89..,

So you have a solution from elsewhere and what, you didn't plan to tell us?!!?!!??? Nice, real nice! {You do know what sarcasm is, don't you?}

NOW PLEASE READ THIS!!!!!

A message to forum cross posters