Geek asks and answers

Delete cells in Excel that have null values

I am trying to remove / hide cells whose values ​​are 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 deletes the entire line. I want to delete the description of the value and value.

+3
source share
1 answer

This code quickly clears (erases) 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
+3
source

More articles:


All Articles