VBA to hide named cell areas in excel

[Anandpersad]

Hi All,
Please help with a VBA to hide / unhide rows based on named cell areas.
I have a dropdown button with the following options:
New > 250 but <750
New >750
Existing >250 but <750
Existing >750
Ending

I have the following named areas:
Pricing : rows 36-57
Customer : rows 62-65
Payment : rows 62-65
History : rows 80-89
Vote : 90-96

I am looking for a VBA to do the following:
New > 250 but <750 choosen, than hide Pricing, Customer, Payment, History and Vote
New >750 choosen, than hide Payment and History hidden. Pricing, Customer and Vote unhidden
Existing >250 but <750 than hide Pricing, Customer and Vote hidden. Payment and History unhidden
Existing >750 choosen, nothing hidden. Showing all rows 1-98
Ending choosen, than rows 35-97 hidden

Thanks in advance for your help.
Anandpersad

 

[AlphaFrog]

I have a dropdown button

Is the dropdown on a userform or on a worksheet?

If it's on a sheet, what type of drop down: Conditional Formatting; ActiveX-type; Form-type?

Depending on the type, what cell is it located in or what is its name?

 

[Anandpersad]

[TABLE="width: 456"]
<colgroup><col></colgroup><tbody>[TR]
[TD]The dropdown button is made on a worksheet called Checklist review page 1[/TD]
[/TR]
[TR]
[TD]The dropdown button is made through: data, data validation, list[/TD]
[/TR]
[TR]
[TD]The dropdown button is in cell H7[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

• Right-click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
[COLOR=green]'I have a dropdown button with the following options:[/COLOR]
[COLOR=green]'New > 250 but <750[/COLOR]
[COLOR=green]'New >750[/COLOR]
[COLOR=green]'Existing >250 but <750[/COLOR]
[COLOR=green]'Existing >750[/COLOR]
[COLOR=green]'Ending[/COLOR]
[COLOR=green]'[/COLOR]
[COLOR=green]'I have the following named areas:[/COLOR]
[COLOR=green]'Pricing:  Rows 36 - 57[/COLOR]
[COLOR=green]'Customer:  Rows 62 - 65[/COLOR]
[COLOR=green]'Payment:  Rows 62 - 65[/COLOR]
[COLOR=green]'History:  Rows 80 - 89[/COLOR]
[COLOR=green]'Vote : 90-96[/COLOR]
[COLOR=green]'[/COLOR]
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "H7" [COLOR=darkblue]Then[/COLOR]
        Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Target.Value
            [COLOR=darkblue]Case[/COLOR] "New > 250 but <750"
                [COLOR=green]'hide Pricing, Customer, Payment, History and Vote[/COLOR]
                Range("Pricing").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Customer").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Payment").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("History").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Vote").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]Case[/COLOR] "New >750"
                [COLOR=green]'hide Payment and History hidden. Pricing, Customer and Vote unhidden[/COLOR]
                Range("Pricing").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Customer").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Payment").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
                Range("History").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
                Range("Vote").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]Case[/COLOR] "Existing >250 but <750"
                [COLOR=green]'hide Pricing, Customer and Vote hidden. Payment and History unhidden[/COLOR]
                Range("Pricing").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Customer").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
                Range("Payment").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
                Range("History").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
                Range("Vote").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
            [COLOR=darkblue]Case[/COLOR] "Existing >750"
                [COLOR=green]'nothing hidden. Showing all rows 1-98[/COLOR]
                Range("1:98").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
            [COLOR=darkblue]Case[/COLOR] "Ending"
                [COLOR=green]'rows 35-97 hidden[/COLOR]
                Rows.Hidden = [COLOR=darkblue]False[/COLOR] [COLOR=green]'Unhide all rows[/COLOR]
                Range("35:97").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
        Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Anandpersad]

Hi Sir / Madam,

Thank you very much for your help.
This is working perfectly