Checkbox to hide/unhide if zero

[DaveAtlanta]

I am trying to set a check box to hide rows that have a zero value formula. I was able to piece together this macro to hide, but can't seem to figure out how to unhide when the check box is unchecked.

If CheckBox15 = False Then
Dim i As Integer
With Range("r6")
For i = 1 To 54
If .Offset(i, 0) <> 0 Then
.Offset(i, 0).EntireRow.Hidden = False
Else
.Offset(i, 0).EntireRow.Hidden = True
End If
Next
End With
End If

I would appreciate any suggestions and advise.
Dave
in Atlanta

 

[LTunnicliffe]

Hello Dave,

What about something like:

If CheckBox15 = False Then                       ' if Check Box is unchecked then unhide everything
      Range("R6:R60").EntireRow.Hidden = False
    Else                                             ' else hide zeroes
      Range("R6:R60").Replace "0", "", xlWhole                 'change 0's to blanks
      Range("R6:R60").SpecialCells(xlCellTypeBlanks).Select    'select blanks
      Selection.EntireRow.Hidden = True                        'hide blanks
      Selection.Value = 0                                      'put 0's back
    End If

HTH,

 

[DaveAtlanta]

Thanks, but that's not quite what I need.

My totals column is all formulas which I don't want to replace. I was hiding the rows manually using the Ctrl-9, then unhiding manually. But now that it has grown I would like a macro using a check box to look at the totals and all the zeros hide, but still being able to uncheck the checkbox to re-display the rows that had a zero value.

thanks
Dave