(Excel VBA) Range Selection Base on criteria and deleting

(Excel VBA) I am attempting to delete all rows in one column (B) that contain the value of "RET" I have already written the program to delete each row with "RET" or "--" or "Wh" or Len(ActiveCell.Value) = 4 or Len(ActiveCell.Value) = 1 etc etc ASCII characters

The problem is that the "RET" value populates apprx. 40,000 rows. Subsequently, the program runs kind of slow (15-20mins) when deleting every single row. After this deletion process, I usually end up with 13,000 rows. A 2002 Excel worksheet contains appx. 65,000 rows. How would you delete apprx. 40,000 rows in the least amount of time within a "For next" statement? When the range of RET's are selected VB will not let me Selection.EntireRows.Delete
Why?

Thanks

 

[IRstuff]

VBA is SLOW, so a for-next for 40,000 iterations is probably doomed, unless you're running a 3 GHZ P4.

Alternate, you could try numbering each row with an index, sorting for your search term, delete the rows as a group and then re-sort on the index. You could do that by hand in about a minute or so.

TTFN

 

[jproj]

Hank0312:

At the begining of your program (before you start deleting things), turn Excel's "Enable Calculation" off:

Worksheets("Sheet1&quot.EnableCalculation = False

When you are done (at the end of your program), turn it back on:

Worksheets("Sheet1&quot.EnableCalculation = True

This should significantly reduce your program run time.

Good Luck!

Jproj

 

[dwilson]

Working off IRstuff's idea, try this, it assumes that column B contains the RET values, this can easily be changed.

Sub deleteRET()

Dim strTemp As String, rngSearch As Range
Dim firstaddress As Range, lastaddress As Range

Cells.Select
Selection.Sort Key1:=Range("B1")
strTemp = "ret"
Set rngSearch = ActiveSheet.UsedRange.Columns(2)
rngSearch.Select

With rngSearch
  Set c = .Find(strTemp, LookIn:=xlValues)
  If Not c Is Nothing Then
    Set firstaddress = c
    Do
      Set lastaddress = c
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress.Address
  End If
End With

Rows(firstaddress.Row & &quot;:&quot; & lastaddress.Row).Select
Selection.Delete shift:=xlShiftUp
Cells.Select
Selection.Sort Key1:=Range(&quot;A1&quot;)
Cells(1, 1).Select

End Sub

It sorts then determines the first and last rows containing "RET" and deletes all row the contain "RET". It then resorts the data using column A.

Let me know if this does a better job than for...next

Dave