Row Deletion Macro

[Ji.singh]

Hi Ninjas,
i have an excel sheet of attendance, i want to delete all rows which are inactive, rows are about 2000, pls suggest a macro which can delete all rows where status is "Inactive".

A sample is attached.

 

[shrivallabha]

Try untested (simple for loop):

Private Sub DeleteRows()
Application.ScreenUpdating = False
For I = Range("B" & Rows.Count).End(xlUp).Row to 2 step -1
     If Range("B" & I).Value = "Inactive" then Range("A" & I).EntireRow.Delete xlUp
Next I
Application.ScreenUpdating = True
End Sub

 

[Chihiro]

Here's another method. Depending on your actual workbook set up, it will be faster since there is single delete operation at end.

Though if it's simple workbook with no calculation etc, shrivallabha's code is plenty fast (should take less than a second for ~2000 rows).

Private Sub DeleteRows()
Dim I As Long
Dim dRng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For I = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("B" & I).Value = "Inactive" Then
        If dRng Is Nothing Then
            Set dRng = Range("A" & I)
        Else
            Set dRng = Union(dRng, Range("A" & I))
        End If
    End If
Next I

dRng.EntireRow.Delete

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Note: There is no need to reverse loop direction for this one. But I was too lazy to change from shrivallabha's code

 

[Ji.singh]

hi Ninjas,
very very thank you,
I am not very aware about VBA, but Shrivallabha's solution was some difficult for me, but i tried @Chihiro's code, it works and my problem solved.
now I am trying to understand first solution by shrivallabha.

 

[Marc L]

Hi !

Easy way for a beginner : via a filter !

Fastest way the more isolated lines to delete :
a sort on an additionnal column (False to keep line, True to delete it)
then clear - and not delete ! - at once the True block …
​