VBA Help - Any way to speed up this code? - Delete Rows Based on Cell Value

[Johnny Thunder]

Hi Gang,

Wanted to see if anyone has a way of speeding up a working code. Currently it takes 3-5min on my PC to run this code. Was hoping for a more time efficient update to make this faster, any suggestions are appreciated.

What it does: Looks at column B on my worksheet and finds any cells with the value 0 and deletes the entire row. It starts by passing the headers and looking at my data range from (B7:Bxxxx). For my test the data went from B7:B9007 and expands from column A:J.

Here is the working code:

Sub DeleteRows()


Dim r As Long
Dim FirstRow As Long
Dim LastRow As Long


'Need to avoid the top header rows
FirstRow = 7
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For r = LastRow To FirstRow Step -1
If Cells(r, "B") = 0 And Cells(r, "B") = 0 Then
Rows(r).Delete 


End If


Next r


End Sub

 

[JoeMo]

This should be faster:

Sub DeleteRows()
Dim FirstRow As Long
Dim LastRow As Long
Dim V As Variant, i As Long
FirstRow = 7
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
With Range("B" & FirstRow, "B" & LastRow)
    V = .Value
    For i = 1 To UBound(V, 1)
        If V(i, 1) = 0 Then V(i, 1) = "#N/A"
    Next i
    .Value = V
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

 

[Johnny Thunder]

@JoeMo That was loads faster, it literally flickered and it was done. I learn something new everyday! Thanks

This should be faster:

Sub DeleteRows()
Dim FirstRow As Long
Dim LastRow As Long
Dim V As Variant, i As Long
FirstRow = 7
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
With Range("B" & FirstRow, "B" & LastRow)
    V = .Value
    For i = 1 To UBound(V, 1)
        If V(i, 1) = 0 Then V(i, 1) = "#N/A"
    Next i
    .Value = V
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

@JoeMo That was loads faster, it literally flickered and it was done. I learn something new everyday! Thanks

You are welcome - thanks for the reply.