CODEWITHSUNDEEP
excelvba
xtyper
xtyper

Reputation: 13

How to delete row if cell shows 0 but has formula?

Sub DeleteRow()
Dim r As Long
Dim FirstRow As Long
Dim LastRow As Long

FirstRow = 3
LastRow = Cells(Rows.Count, "B").End(xlUp).Row - 1
For r = LastRow To FirstRow Step -1
    If Cells(r, "B") = 0 Then
        Rows(r).Delete
    End If
Next r
End Sub

It does work where the cell shows 0 and does not have a formula.

When I try to delete rows where column B has cells that have 0 and have formulas, it gives

Type mismatch

Upvotes: 1

Views: 94

Answers (1)

VBasic2008
VBasic2008

Reputation: 54983

Delete Rows

Option Explicit

Sub deleteRows()
    
    Const FirstRow As Long = 3
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    Dim trg As Range
    Dim cel As Range
    Dim cValue As Variant
    Dim r As Long
    
    For r = FirstRow To LastRow
        Set cel = ws.Cells(r, "B")
        If Len(cel.Formula) > 1 Then
            cValue = cel.Value
            If Not IsError(cValue) Then
                If cValue = 0 Then
                    If trg Is Nothing Then
                        Set trg = cel
                    Else
                        Set trg = Union(trg, cel)
                    End If
                End If
            End If
        End If
    Next r
    
    If Not trg Is Nothing Then
        trg.EntireRow.Delete
    End If

End Sub

Upvotes: 2

Related Questions