Macro to hide a section (I saw a similar post)

ntruong

Active Member
Joined
Aug 4, 2003
Messages
261
Hi Expect,
My question is similar to the one posted by digbal but the bit difference is:
I have a range of rows (20-30) named "distribution"
My purpose is --> if cell C1 is "yes" then the section "distribution" that consists of 10 rows should be unhidden. By default, I will hide these rows, and C1 is defaulted to "no".
Thanks a lot in advance,
Nee
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hello Nee,

Code:
'Place these procedures in the Worksheet module.

Private Sub Worksheet_Activate()
    HideUnHideNamedRange
End Sub
'----------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address(0, 0) = "C1" Then Exit Sub
    HideUnHideNamedRange
End Sub
'----------------------------------------------------------------------
Private Sub HideUnHideNamedRange()
    [distribution].EntireRow.Hidden = (StrConv([C1].Value, 1) <> "YES")
End Sub
 
Upvote 0
Re: Macro to hide a section (Thanks, Colo!)

Thanks so much Colo and readers. I'll cross my finger and plug your codes in my ws.
Best Regards,
Nee
 
Upvote 0
Hello Colo & Others
I pasted the following codes in my spreadsheet and I have a compile error (syntax error). The complaint is that we do not have an "endif" in the 2nd section. Also, do the dashed line work for the module?

Thanks again,
Nee


Private Sub Worksheet_Activate()
HideUnHideNamedRange
End Sub
'----------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Address(0, 0) = "C1" Then Exit Sub
HideUnHideNamedRange
End Sub
'----------------------------------------------------------------------
Private Sub HideUnHideNamedRange()
[distribution].EntireRow.Hidden = (StrConv([C1].Value, 1) <> "YES")
End Sub
 
Upvote 0
ntruong said:
Hello Colo & Others
I pasted the following codes in my spreadsheet and I have a compile error (syntax error). The complaint is that we do not have an "endif" in the 2nd section. Also, do the dashed line work for the module?


Make sure you paste the code in the right place, you shoudl right click on teh sheet tab that you want the code to run for and then select VIEW CODE, paste the code exactly as it appears above and it should work. I didn't have to modify the code at all to get it to hide the named range distribution.

you shouldn't need to add an end if on teh 2nd line because the if statement is self contained on the one line.
 
Upvote 0
Re: Macro to hide a section (Thanks Again)

Hello Colo / Buddie,

I must have done something dumb the 1st time ..
Now I tried Colo's codes again and they work beautifully. I'm so appreciative. Thanks again Colo and thank you Buddy for leading me to the right spot.

Nee
 
Upvote 0

Forum statistics

Threads
1,226,497
Messages
6,191,372
Members
453,655
Latest member
lasvegasbuffet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top