VBA Hide Columns Based on Criteria

Question

I'm having a bit of trouble with some VBA code in achieving the desired outcome.

Example

What I am trying to achieve with the example above is to, with the push of a toggle button the following will happen:

1. All Rows with no data OR $0 in the most recent 3 columns (DEC, NOV, and OCT in the example) will be hidden. (Green cells).
2. Rows with at least one value in the 3 most recent columns will remain visible (Blue Cells)
3. Rows with all cells filled with a value above $0 will be visible (Yellow Cells).

Then, I need another toggle button to unhide everything in the event one of the hidden rows has a new charge.

BACKGROUND:

We own apartment buildings, and when the apartments are vacant, we must pay the utilities. When someone rents the apartment, we will not pay them. We want to track history and trends for when they are vacant, and when they are not vacant, we want them to be hidden to cut down on scrolling.

PROPOSED OPERATION:

In practice, we would like to see the following occur:

1. Invoices received.
2. One of the invoices references a hidden property.
3. Manager presses "Unhide All" toggle button.
4. Manager enters values.
5. Manager presses "Hide Unused" toggle button.
6. All rows with no values in current and previous 2 months (3 months total) will be hidden.

My VBA below does not hide the cells properly.

Private Sub ToggleButton1_Click()

If ToggleButton1 Then
    MsgBox "All Rows Unhidden."
    Rows("1:1000").EntireRow.Hidden = False
End If

End Sub

Private Sub ToggleButton2_Click()

If ToggleButton2 Then
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In Me.Range("B3:B1000, C3:C1000, D3:D1000")
        If c.Value = 0 Or c.Value = "" Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End If

End Sub

This formula for ToggleButton2 does not work and misses values unless they are in range D3:D1000.

Answer 1

Try this; it loops down the rows and checks all three cells in the row for your criteria, if it meets the criteria it will hide the row.

Dim i As Long
For i = 3 To 1000

    If (Range("C" & i) = "" Or Range("C" & i) = "0") _
        And (Range("D" & i) = "" Or Range("D" & i) = "0") _
        And (Range("E" & i) = "" Or Range("E" & i) = "0") Then
        Range("A" & i).EntireRow.Hidden = True
    End If

Next i

Answer 2

As written, Me would refer to ToggleButton2, correct? I would think you would want to refer to the sheet itself, a la:

Edit: Ohh, I see what's happening. As written, if ANY of the cells are wrong, it'll hide. This version will change it so if ANY of the cells AREN'T wrong, it WON'T hide. Hopefully this does the trick. (Also it's a lot faster)

Private Sub ToggleButton2_Click()
Dim ws As Worksheet, x(2) As Long, y(3) As Long, z As Long

Set ws = ActiveWorkbook.ActiveSheet

x(1) = 3: x(2) = 1000
y(1) = 2: y(2) = 3: y(3) = 4

Application.ScreenUpdating = False

If ToggleButton2 Then

    For z = x(1) To x(2)

        If _
        IsValid(ws.Cells(z, y(1)).Value) Or _
        IsValid(ws.Cells(z, y(2)).Value) Or _
        IsValid(ws.Cells(z, y(3)).Value) Then
            ws.Rows(z).Hidden = False
        Else
            ws.Rows(z).Hidden = True
        End If

    Next z

End If

Application.ScreenUpdating = True

End Sub

'--------

Function IsValid(val) As Boolean
If val = 0 Or val = "" Then
    IsValid = False
Else
    IsValid = True
End If
End Function

Answer 3

One Button Only (Toggle)

In your code, the line For Each c In Me.Range("B3:B1000, C3:C1000, D3:D1000") with the part B3:1000 indicates that the range to be processed starts with the second column (B), but in the picture it looks like the range starts with the third column (C). Therefore:

Before using this code carefully read through the customize section where you have to change the values to fit your needs.

Option Explicit

Private Sub ToggleHideShow_Click()

Application.ScreenUpdating = False

'--Customize BEGIN ---------------------
    Const ciRowStart As Integer = 3 'First row of data
    Const ciRowEnd As Integer = 1000 'At least the last row of data.
    Const ciColStart As Integer = 3 'First column of data
    Const ciColEnd As Integer = 5 'Last column of data
'--Customize BEGIN ---------------------

    Dim iRowCounter As Integer
    Dim iColCounter As Integer
    Dim oRng As Range

  If ToggleHideShow.Caption = "Show" Then

    Set oRng = Range(ciRowStart & ":" & ciRowEnd)
    oRng.EntireRow.Hidden = False

    ToggleHideShow.Caption = "Hide"

    MsgBox "All Rows Unhidden." 'I would get rid of it.

   Else

    For iRowCounter = ciRowStart To ciRowEnd
      For iColCounter = ciColStart To ciColEnd
        Set oRng = Cells(iRowCounter, iColCounter)
        If oRng.Value = 0 Or oRng.Value = "" Then
          oRng.EntireRow.Hidden = True
          Exit For 'To immediately go to the next row.
        End If
      Next
    Next

    ToggleHideShow.Caption = "Show"

  End If

Application.ScreenUpdating = True

End Sub

The concept of a toggle button is to have one button only which will change the state of something when being clicked on, e.g. on click make a font bold and on the next click make it back normal and on the next click again bold, and on the next click again normal and so on.