VBA Code to Hide Rows with zero values in two cells

Question

Hello,

Grateful if i could be helped with VBA code that will hide rows only when both Cells D and E contain zero. I tried the following codes but it hides rows if even one of the cells has zero value:

Sub HideRows()

    Dim cell As Range

    For Each cell In Range("C2:E7")

        If Not IsEmpty(cell) Then

            If cell.Value = 0 Then

                cell.EntireRow.Hidden = True

            End If

        End If

    Next

End Sub

A	B	C	D	E
Acct Code	Details of Transaction	Debit	Credit	Balance
1001	Cash Deposit	1,500	0	1,500
1002	Cash Withdrawn	0	800	700
1003	Transfer	0	0	700
1001	Cash Deposit	300	0	100
1004	Commission	0	0	100
1002	Cash Withdrawn	0	200	800

In the above example, Rows 4 and 6 (Transfer and Commission) will be hidden. 

Thanks in advance for your assistance.

Dominic

Answer 1

Hi,

try this simple code...

(hide rows if cells in columns C and D are Zero)

Sub macro_01()

Dim x As Double

Dim RowFirst As Long

Dim RowLast As Long

Dim i As Long

RowFirst = 2

RowLast = 7

For i = RowFirst To RowLast

x = Cells(i, "C").Value + Cells(i, "D").Value

If x = 0 Then Rows(i).Hidden = True Else Rows(i).Hidden = False

Next

End Sub

Answer 2

You mention columns D and E but your example shows C and D with zeros.

This macro will hide rows where C and D columns both have zeros.

Sub Hide_C_D_Zero()

    Dim rngCell As Range

    Dim Rng As Range

    Set Rng = ActiveSheet.Range("C2:C10")

    For Each rngCell In Rng.Rows

        If Trim(rngCell) & rngCell.Offset(, 1) = 0 Then

            rngCell.EntireRow.Hidden = True

        End If

    Next rngCell

End Sub

Gord

Answer 3

Just to be different, here is a non-looping macro that will also work...

Sub ClearOthers()

  Dim LastRow As Long

  LastRow = Cells(Rows.Count, "A").End(xlUp).Row

  Range("F2:F" & LastRow) = Evaluate(Replace("IF(C2:C#+D2:D#,"""",""X"")", "#", LastRow))

  On Error GoTo NoDoubleZeroes

  Columns("F").SpecialCells(xlConstants).EntireRow.Hidden = True

  Columns("F").Clear

NoDoubleZeroes:

End Sub