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.
Answers
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