Macro/VBA button help

Not applicable

Greetings!  I'm hoping someone can help with a project I'm currently working on.  I'm revamping a spreadsheet for work that we use to track hours spent on a project.  I have a table that contains the names of all of our employees and their hours. I want to make a button that we can click to hide any rows containing employees that have zero hours on the project (total hours are in column I).  So far, I have created a toggle button using the activeX controls (Just one button? should I be seeing two?) and added this code to the button:


Option Explicit

Private Sub ToggleButton1_Click()
With Me
If .ToggleButton1.Value = False Then
If .AutoFilterMode Then .UsedRange.AutoFilter
.ToggleButton1.Caption = "Hide 0's"
.UsedRange.Columns(9).AutoFilter Field:=1, Criteria1:="0"
.ToggleButton1.Caption = "Show 0's"
End If
End With
End Sub


When I click the button however, I get this error:

Run-time error '1004':

AutoFilter method of Range class failed


I'm a bit new to this more advanced level excel stuff, so my technique is pieced together from various things I've found online.  I can't seem to find any reason why it isn't working, so I'm hoping a helpful online community can assist me in figuring it out!


Any advice would be super appreciated!


1 Reply


Solved!  A kindly Reddit user helped me out and thought I'd share it here:

Col - Column where you're looking for zero values, "A" in the example

UpR - Upper range, last row of data

LowR - Lower range, first row of data

Private Sub ToggleButton1_Click()

Set WS = Worksheets("Sheet1")
Col = "A"
UpR = 25
LowR = 1

If ToggleButton1.Value = True Then
    With WS
        For FR = UpR To LowR Step -1
            If .Range(Col & FR) = 0 Then
                .Rows(FR).Hidden = True
            End If
        Next FR
    End With
End If

If ToggleButton1.Value = False Then
    WS.Rows.EntireRow.Hidden = False
End If

End Sub