gisvpn
asked on
Is there a better way to do this?
I have this code but it does take a little time to execute - is there a better approach to this?
Dim lRow
lRow = 3000
Do While lRow >= 2
If Cells(lRow, 9) <> "Successful" Then Rows(lRow).Delete
lRow = lRow - 1
Loop
Whenever deleting rows you should do it backward
Dim lRow
For lRow = 3000 to 2 step -1
If Cells(lRow, 9) <> "Successful" Then Rows(lRow).Delete
Next
The OP's code already deletes backwards from 3000
Creating a range to delete will be faster
However, AutoFilter will be faster than a Loop.
Note the criteia in my earlier post should be <> "Successful" not ="Successful"
Creating a range to delete will be faster
Option Explicit
Sub DeleteRows()
Dim rRng As Range, rDelete As Range, rCl As Ranges
Set rRng = Cells(1, 9).CurrentRegion '///limits the number of Rows
For Each rCl In rRng
If rCl.Value <> "Successful" Then
If rDelete Is Nothing Then
Set rDelete = rCl
Else: Set rCl = Union(rDelete, rCl)
End If
End If
Next rCl
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub
However, AutoFilter will be faster than a Loop.
Note the criteia in my earlier post should be <> "Successful" not ="Successful"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Open in new window