VBA TIPS
Deleteing rows

As one of frequently asked questions that I've seen on the board, here is the question "how to delete entire rows?".

When you'd like to to create a loop that goes through the first column A and checks to see if the value is"bad", if it is delete the row.



In this case, there are several ways to do this(Looping, using AutoFilter, Sorting....). Here are 3 example procedures that would return the exactly same result with looping.

The first one is so simple and easy to understand. looping from the bottom to the top and if the "bad" is found, deleting that row. But it takes time a little.

The second one is not bad. go through the whole data set and preserving the range(with Union Method:Returns the union of two or more ranges) for deleting, and it would delete the preserved range at the end.

The third one is more faster than the second one. For Each Next looping is used in there. It's faster than For Next Looping.

But as per usual, Delete method takes time. In my humble opinion, probably, it would be better not to use this Delete method as much as possible. If it can be substituted with Sort and Clear methods, I would recommend to use that instead. Moreover, please don't forget that the AutoFilter function is also high-speed.

To know the running speed, I added a message box for each procedure. anyway please give these procedures try.

Place the following in the standard module.

Option Explicit

Sub DeleteingRows1()
'Slow
    Dim s As Single
    Dim rForDelete As Range
    Dim i As Long
    s = Timer
    With Sheets("Sheet1")
        For i = 5000 To 1 Step -1
            If .Cells(i, 1).Value = "bad" Then
               .Cells(i, 1).EntireRow.Delete
            End If
        Next
    End With
    MsgBox "Time is " & Timer - s
End Sub

Sub DeleteingRows2()
'Not bad
    Dim s As Single
    Dim rForDelete As Range
    Dim i As Long
    s = Timer
    With Sheets("Sheet1")
        For i = 1 To 5000
            If .Cells(i, 1).Value = "bad" Then
                If rForDelete Is Nothing Then
                    Set rForDelete = .Cells(i, 1)
                Else
                    Set rForDelete = Union(rForDelete, .Cells(i, 1))
                End If
            End If
        Next
    End With
    If Not rForDelete Is Nothing Then rForDelete.EntireRow.Delete
    MsgBox "Time is " & Timer - s
End Sub

Sub DeleteingRows3()
'Fast
    Dim s As Single
    Dim rForDelete As Range
    Dim c As Range
    Dim i As Long
    s = Timer
    With Sheets("Sheet1")
        For Each c In .Range(.Cells(1, 1), .Cells(5000, 1))
            If c.Value = "bad" Then
                If rForDelete Is Nothing Then
                    Set rForDelete = c
                Else
                    Set rForDelete = Union(rForDelete, c)
                End If
            End If
        Next
    End With
    If Not rForDelete Is Nothing Then rForDelete.EntireRow.Delete
    MsgBox "Time is " & Timer - s
End Sub





| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo