Hiding rows depending on the value of a cell picked from a drop down menu

**orly_sm** · 11-10-2012, 11:05 AM

In the attached BASE xls I want to be able to hide certain rows according to the value of C2 (representing Model) which is selected from a drop down menu. (values 1-4 representing,say, low to high price cars)

So for Model (C2) = 1 (low price) I would want to exclude Row 32 (sunroof) and Rows 20-23 inc (alloys) and Row 10 (Hybrid engine) as they are not an option on Model 1
For Model (C2) = 2 (low-mid ) I would want to exclude Row 32 (sunroof) and Rows 22-23 inc (some alloys)) as they are not an option on Model 2
For Model (C2) = 3 (mid-high) I would want to exclude no Rows as all options are available on Model 3
For Model (C2) = 4 (high price) I would want to exclude Rows 18 and 19 (steel wheels) and 26 and 27 (Flat colours) as they are not an option on Model 4

Already set - the Actual cost must be between 0 and the max Cost in Column C. The sheet is protected but there is no password.

I need the list to reinstate rows when moving from one model to another (that may be automatic with the function - I don't know)

I need the Total row to only sum the values in the shown Rows so that the calculated Percentage is correct based on the options chosen and the Actual price paid

I guess this is a Macro or a function but I'm useless at them so you help would be appreciated - even down to showing me how to attach the macro or whatever to the cell C2. I would like the rows selection to be driven by the Model value in C2

Thanks for your help

**JBeaucaire** · 11-10-2012, 02:14 PM

1) Right-click the sheet tab and select VIEW CODE

2) In the sheet code module that appears, paste in this sheet-event macro:

Please Login or Register  to view this content.

3) Read through the code so you can get a feel for what it does.

4) Close the VB editor and save your sheet as a macro-enabled workbook... Base.xlsm

5) I've updated the TOTALS formulas to SUBTOTALS() so they will only sum visible rows.

Now make changes in C2.

**orly_sm** · 11-10-2012, 02:37 PM

Hi JB

Thanks for your well-explained solution. I did everything you said which were things I've never done before in Excel (eg using the View Code on the Sheet tab and saving it as an .xlsm) but the first time I changed Cell C2 it gave a value error and reported the Range("A7:A30").EntireRow.Hidden = False 'unhide all rows to reset so I changed it to A5:A32 thinking the range wasn't right, saved it again but while that cleared the error it didn't produce the expected result so I reset you code and saved it and it doesn't seem to have any effect when I change the value in C2.

The thing is I can really follow your code so will explore later (I'm off to work - work permanent nights) to see if I can resolve it.

Could you explain the use of Subtotal vice Total to sum only visible rows which is definitely what I need. In fact could you upload you version of my spreadsheet so I can follow it through.

Thanks again JB - really appreciate your help

Andy

**JBeaucaire** · 11-10-2012, 03:21 PM

Duh, I unprotected your sheet and forgot you protected it.

Please Login or Register  to view this content.

You can press F1 in Excel and read up on SUBTOTAL.

**orly_sm** · 11-11-2012, 10:48 AM

Hi Jerry

thanks again for your help. I got it to work last night at work on a different version of Excel and now back at home continuing to develop it in my newer home version and it's coming along OK but slow. The main thing is I've learned quite a bit of new stuff - eg I would never have worked out SUBTOTAL(109.C5:C30) as the mechanism for summing only unhidden lines without your pointer. And it was also my first delve into VB and macros. So thanks for that. I'm sure I'll be back with another poser when I hit another wall. Thanks again, Jerry
Andy