Multiple "Ifs" to Hide Rows in a Macro

corcelle · Mar 5th 2018

Hello,

I'm attempting to write a macro that will display results based on the entry in a few fields. I've programmed the workbook to hide pertinent rows upon opening the file. I want it to then unhide rows based on the value of cell B2, which is a drop-down menu with 15 options.

If option "A" is selected, I want it to unhide rows 7 & 8
If option "B" is selected, I want it to unhide rows 9 & 10

. . . and so forth.

I also want to make sure that as the value of cell B2 is changed, only the corresponding rows are displayed (so previously unhidden rows need to be rebidden).

I can't seem to figure out how to put more than one if/else statement in succession to make this happen!

All help is much appreciated.

Thank you!

Mumps · Mar 5th 2018

Are the values in the drop down list in B2: A, B, C, D, etc.? If not, what are they exactly? Will the rows to hide upon a selection in B2 follow the pattern 7 & 8, 9 & 10, 11 & 12, 13 & 14, etc.?

**KjBox** · Mar 5th 2018

Is the dropdown cell validation, a form control combobox or an ActiveX combobox?

corcelle · Mar 6th 2018

Hi Mumps - Thanks for responding.

No, the values are not "A, B, C" - they are a list of marketing programs for my company. There are 15 of them, of varying lengths.
Actually, upon further review, the pattern should be to ALWAYS unhide row 7, then, in order, unhide 8 - 22 with their corresponding selection.

KjBox -- also thank you!
The dropdown is currently a list validation.

corcelle · Mar 6th 2018

PS -- I plan to have a button to press for "Show Results" that the macro would be assigned to.

corcelle · Mar 6th 2018

Sorry -- I just realized I can just leave Row 7 unhidden upon open. Duh. So, I really just need to unhide/rehide rows 8-22 based on the value of cell B2.

Mumps · Mar 6th 2018

In order for this to work, we have to use a helper cell to store the row number of the row which was unhidden previously. I have used cell AA1 in the code, assuming that this cell in your worksheet will never be used. If you will be using cell AA1 at any time in your sheet, change all occurrences of Range("AA1") in the code to a range which you know will never be used. I am also assuming that your sheet will start with rows 8 to 22 as hidden. Enter the number 8 in AA1 (or whatever cell you may change it to). You will also have to change the 'Case' values in the macro (A, B, C, D, E) to the actual 'marketing program' names. I have included only 5 'Case' statements. You will have to add all the other 14 following the pattern in the existing code. This macro is a Worksheet_Change event so it will be triggered automatically when you make a selection in cell B2. You don't need a button to run it. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B2.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Target.Value
        Case "A"
            ActiveSheet.Rows(Range("AA1").Value).Hidden = True
            ActiveSheet.Rows(8).EntireRow.Hidden = False
            Range("AA1") = 8
        Case "B"
            ActiveSheet.Rows(Range("AA1").Value).Hidden = True
            ActiveSheet.Rows(9).EntireRow.Hidden = False
            Range("AA1") = 9
        Case "C"
            ActiveSheet.Rows(Range("AA1").Value).Hidden = True
            ActiveSheet.Rows(10).EntireRow.Hidden = False
            Range("AA1") = 10
        Case "D"
            ActiveSheet.Rows(Range("AA1").Value).Hidden = True
            ActiveSheet.Rows(11).EntireRow.Hidden = False
            Range("AA1") = 11
        Case "E"
            ActiveSheet.Rows(Range("AA1").Value).Hidden = True
            ActiveSheet.Rows(12).EntireRow.Hidden = False
            Range("AA1") = 12
    End Select
    Application.ScreenUpdating = True
End Sub

Display More

Multiple "Ifs" to Hide Rows in a Macro

Participate now!

Share