(Excel VBA) Range Selection Base on criteria and deleting
(Excel VBA) Range Selection Base on criteria and deleting
(OP)
(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
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





RE: (Excel VBA) Range Selection Base on criteria and deleting
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
RE: (Excel VBA) Range Selection Base on criteria and deleting
At the begining of your program (before you start deleting things), turn Excel's "Enable Calculation" off:
Worksheets("Sheet1").EnableCalculation = False
When you are done (at the end of your program), turn it back on:
Worksheets("Sheet1").EnableCalculation = True
This should significantly reduce your program run time.
Good Luck!
Jproj
RE: (Excel VBA) Range Selection Base on criteria and deleting
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 & ":" & lastaddress.Row).Select
Selection.Delete shift:=xlShiftUp
Cells.Select
Selection.Sort Key1:=Range("A1")
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