CODEWITHSUNDEEP
vbaexcel
sean
sean

Reputation: 9268

Remove cells in Excel which have zero values

I have been trying to remove/hide cells which values are equal to zero (0). 

Sub HideRows()
    Dim cell As Range, rng As Range
    Cells.Rows.Hidden = False

    On Error Resume Next
    Set rng = Columns(5).SpecialCells(xlConstants, xlNumbers)
    On Error GoTo 0

    For Each cell In rng
        If cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        End If
    Next

End Sub

The code removes the entire row. I want to remove the description of the value and the value.

Upvotes: 3

Views: 9806

Answers (1)

brettdj
brettdj

Reputation: 55702

This code will quickly clear (erase) values and comments from cells in column E that have a value of 0

Sub Testme()
      Dim rng1 As Range
Set rng1 = Columns(5)
With rng1
    .AutoFilter 1, "0"
    With rng1.Offset
        .ClearContents
        .ClearComments
    End With
    With rng1.Offset(0, -1)
        .ClearContents
        .ClearComments
    End With
End With
End Sub

Upvotes: 3

Related Questions