VBA To Hide or Show Rows

[rbell22]

Hi All,

Thanks in advance for the help.

I need to write a macro to do two things. My goal is to have it run so that if you click the button all cells in range G9:G125 that are equal to 0, their corresponding rows will hide. When you click the button again, all rows from 9:125 will show.

Not sure if that is even possible, but I thought I'd give it a shot.

Thanks again

[JKwan]

give this a try
create a button and leave the control name as CommandButton1

Sub Toggle()
    Dim rRange As Range
    Dim rCell As Range
    Dim lRow As Long
    
    Application.ScreenUpdating = False
    Set rRange = Range("G9:G125")
    For Each rCell In rRange
        If rCell = 0 And Sheet1.CommandButton1.Caption = "Hide" Then
            rCell.EntireRow.Hidden = True
        Else
            rCell.EntireRow.Hidden = False
        End If
    Next rCell
    
    If Sheet1.CommandButton1.Caption = "Hide" Then
        Sheet1.CommandButton1.Caption = "Show"
    Else
        Sheet1.CommandButton1.Caption = "Hide"
    End If
    Application.ScreenUpdating = True
End Sub

by the way, first time you run it, probably "won't" do anything, the second click will actually trigger the macro correctly. This is because your button caption may not be setup properly, unless you edit the button first...

[rbell22]

Hi JKwan

Thanks for responding but unfortunately it didn't work. I'm not too familiar with writing the code, so I can make any tweaks. Here's what I did, and maybe you can offer some insight:

-Inserted an ActiveX Control Button
-Right Click on button and selected 'View Code'
-Pasted above code into module
-Saved and closed out of VBA window
-Clicked on the button
-It did not work, Excel opened up VBA window and a pop up showed Compile Error: Expected End Sub

I know I'm asking a lot to have you walk me through this, so I thank you very much. I really appreciate it.

[JKwan]

Here is the complete work

[rbell22]

JKwan,

Your file is perfect and exactly what I am looking for. Unfortunately, when I try and put that into my file, it does not work. I've tried a few different ways to 'copy and paste', but nothing has worked. Do you have any thoughts?

Thanks again in advance.

[JKwan]

Well, I guess you did it incorrectly.... If the data is not sensitive in nature, you can attach it and I can put the code into the sheet for you.

[rbell22]

Hello Again,

I put this on the shelf for a bit, but now I'm working on this again. I still cannot seem to make it work, even though your file worked perfectly. I have attached a sample of what the sheet looks like.

Any help would be much appreciated.

Thanks!

[JKwan]

give this a spin and see.

[rbell22]

That is perfect. You are a life saver!

[JKwan]

Glad I can help