Jul 02 2019 10:17 AM
Jul 02 2019 10:17 AM
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"
Else
.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!
Jul 02 2019 10:53 AM
Jul 02 2019 10:53 AM
@Deleted
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