×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

(Excel VBA) Range Selection Base on criteria and deleting

(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

RE: (Excel VBA) Range Selection Base on criteria and deleting

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

RE: (Excel VBA) Range Selection Base on criteria and deleting

Hank0312:

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

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 & ":" & 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources