PDA

View Full Version : [SOLVED] VBA To Hide or Show Rows



rbell22
12-01-2016, 12:42 PM
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
12-01-2016, 02:51 PM
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
12-01-2016, 03:47 PM
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
12-01-2016, 05:17 PM
Here is the complete work

rbell22
12-02-2016, 07:22 AM
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
12-02-2016, 08:35 AM
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
01-18-2017, 08:02 AM
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
01-18-2017, 08:35 AM
give this a spin and see.

rbell22
01-18-2017, 09:20 AM
That is perfect. You are a life saver!

JKwan
01-18-2017, 11:15 AM
Glad I can help