Multiple "Ifs" to Hide Rows in a Macro

  • 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!

  • 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.?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.

  • 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.

  • 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.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!