Hide/unhide different rows based on values in multiple cells

foleycormac

New Member
Joined
Apr 9, 2015
Messages
15
Office Version
  1. 2019
Hi All.

I really haven't a clue about VBA but I managed to figure out how to hide/unhide a group of rows based on the value of a particular cell.
However, I need to hide/unhide multiple groups of cells based on a yes/no value in other cells.

For example, if C12 is "No" then rows 13:18 are hidden.
If C20 is "No" then rows 21:36 are hidden.

There are 12 sets of rows I need to hide if the associated cell is "No"
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
I assume that if you have code that is working now, you have an IF...THEN block in the code that does this.
Since each "No" is in a different cell, you will need a separate IF...THEN block for each one, unless there is a distinct pattern that is repeatable.
So it really should just be the case of repeating the IF...THEN block you have now, change the ranges in each one.

If you need help, post the code you have now, along with examples of a few more blocks (conditions) so we can see if there is a pattern we can work with to maybe shorten it.
 

foleycormac

New Member
Joined
Apr 9, 2015
Messages
15
Office Version
  1. 2019
Thanks Joe.

I was able to get some code from a Youtube video and adapt it to hide the first block of rows I need to either hide or unhide.
I can't figure out how to mesh the same code together for further rows, do I just add them in after the first 2 Case Is lines?


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C12"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("13:16").EntireRow.Hidden = True
Case Is = "Yes": Rows("13:16").EntireRow.Hidden = False
End Select
End If
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
Case statements work great if you are checking one single cell, which could have many different values, and you want different things to happen in each one.
(i.e. If C1=1, do this, if C1=2, do that, if C1=3, do something else,...)

But if you are checking lots of different cells, then the Case statement doesn't really work as well, and you are better of using IF...THEN blocks, i.e.
VBA Code:
If Range("C1")=1 Then
'    hide these rows
End If

If Range("D1")=1 Then
'    hide those rows
End If

If Range("E1")=1 Then
'    hide some other rows
End If

If all your conditions follow some predictable pattern, we may be able to come up with a dynamic solution that avoids a separate block for each cell you need to check. But we won't really know that unless you can give us at least the first 3 or 4 conditions, so we can try to detect if there is some predictable pattern. If you can provide those exact details, we can help you come up with a solution tailored to your needs.
 

foleycormac

New Member
Joined
Apr 9, 2015
Messages
15
Office Version
  1. 2019

ADVERTISEMENT

Case statements work great if you are checking one single cell, which could have many different values, and you want different things to happen in each one.
(i.e. If C1=1, do this, if C1=2, do that, if C1=3, do something else,...)

But if you are checking lots of different cells, then the Case statement doesn't really work as well, and you are better of using IF...THEN blocks, i.e.
VBA Code:
If Range("C1")=1 Then
'    hide these rows
End If

If Range("D1")=1 Then
'    hide those rows
End If

If Range("E1")=1 Then
'    hide some other rows
End If

If all your conditions follow some predictable pattern, we may be able to come up with a dynamic solution that avoids a separate block for each cell you need to check. But we won't really know that unless you can give us at least the first 3 or 4 conditions, so we can try to detect if there is some predictable pattern. If you can provide those exact details, we can help you come up with a solution tailored to your needs.
 

foleycormac

New Member
Joined
Apr 9, 2015
Messages
15
Office Version
  1. 2019
Test Worksheet.xlsm
BCDEFGHIJKLMNO
2ITEM Total Quantity All Estates UNIT UNIT BASE COST TOTAL BASE COST TypeTypeTypeTypeTypeTypeTypeType
3TypeABCDEFGH
4Quantity1043
5Unit Type Semi-D 2 StoreyMid Terrace 2 StoreySemi-D 2 Storey
6Select House Or ApartmentHouseHouseHouseHouseHouseHouseHouseHouse
73 Bed3 Bed4 Bed
8Required?Required?Required?Required?Required?Required?Required?Required?Required?
9FLOOR INSULATIONYes Total No. of Units NoNoNoNoNoNoNoNo
10Excavate & build back up floor including XTRATHERM THIN-R XT/UF 160mm (2 LAYERS OF 80MM) POLYISO (PIR) U-Value 0.146 W/m2K0 m2 € -
11Supply Insulation 2 layers of 80mm Xtratherm XT/UF 2 layers of 80mm XT/UF (€28/sheet) & 25mm XT/UF perimeter (€16/sheet)0 m2 € -
12Contingency0 m2 € -
13MISCELLANEOUS ITEM - OVERWRITE0 m2 € -
14Total Base Cost: € -
15 Unit Base Cost:
16
17Required?Required?Required?Required?Required?Required?Required?Required?Required?
18ATTIC INSULATIONYes Total No. of Units 8NoNoNoNoNoNoNoNo
19EARTHWOOL 200MM 0 m2 € 15.00€ -
20TANK JACKET (SMALL)0 m2 € 30.00€ -
21TANK JACKET (LARGE)0 m2 € 30.00€ -
22PIPE LAGGING ATTIC 0 m2 € 6.00€ -
23LOFT HATCH (kingspan 100mm retro draft proofing and hooks and eyes0 Nr € 40.00€ -
24WALKWAY SUPPLY & FIT 0 Nr € 30.00€ -
25REMOVE, RAISE % REINSTALL ATTIC SECTION OF FLOORING0 Nr € 38.44€ -
26MECHANICAL FAN INCLUDING INSULATED FLEXI & SLATE VENT0 Nr € 320.00€ -
27INSULATED FLEXI FOR EXISTING BATHROOM MEV0 Nr € 50.00€ -
28ROOF VENT 20,000mm20 Nr € 75.00€ -
29SOFFIT VENTS 2,500mm20 Nr € 3.50€ -
30HLI0 m2 €83.33€ -
31MISCELLANEOUS ITEM - OVERWRITE0 m2 €100.00€ -
32MISCELLANEOUS ITEM - OVERWRITE0 m2 €100.00€ -
33Total Base Cost: € -
34Unit Base Cost: € -
35Required?Required?Required?Required?Required?Required?Required?Required?Required?
36CAVITY WALL INSULATIONNo Total No. of Units NoNoNoNoNoNoNoNo
37CAVITY WALL INSULATION 50MM 0 m2 € 15.00€ -
38CAVITY WALL INSULATION 75MM 0 m2 € 5.50€ -
39CAVITY WALL INSULATION 100MM 0 m2 € 5.50€ -
40CONTINGENCY FOR CHANGING VENT COVERS0 M2 € 20.00€ -
41BACKGROUND VENT0 Nr € 55.75€ -
42PERMANENT VENT0 Nr € 65.00€ -
43MECHANICAL VENT (INCLUDING CORE)0 Nr € 250.00€ -
44NEW CHARCOAL EXTRACTOR0 Nr € 250.00€ -
45CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD0 Nr € 80.00€ -
46MISCELLANEOUS ITEM - OVERWRITE0€ -
47MISCELLANEOUS ITEM - OVERWRITE0€ -
48Total Base Cost: € -
49Unit Base Cost:
50Required?Required?Required?Required?Required?Required?Required?Required?Required?
51INTERNAL WALL INSULATIONNo Total No. of Units NoNoNoNoNoNoNoNo
52INTERNAL WALL INSULATION 82.5MM 0 m2 € 75.00€ -
53CONTINGENCY FOR CHANGING VENT COVERS0 M2 € 20.00€ -
54BACKGROUND VENT0 Nr € 55.75€ -
55PERMANENT VENT0 Nr € 65.00€ -
56MECHANICAL VENT (INCLUDING CORE)0 Nr € 200.00€ -
57NEW CHARCOAL EXTRACTOR0 Nr € 250.00€ -
58CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD0 Nr € 60.00€ -
59MISCELLANEOUS ITEM - OVERWRITE0€ -
60MISCELLANEOUS ITEM - OVERWRITE0€ -
61Total Base Cost: € -
62Unit Base Cost:
63Required?Required?Required?Required?Required?Required?Required?Required?Required?
64EXTERNAL WALL INSULATIONNo Total No. of Units NoNoNoNoNoNoNoNo
65EXTERNAL WALL INSULATION LESS THAN 65M20 m2 € 150.00€ -
66EXTERNAL WALL INSULATION GREATER THAN 65M20 m2 € 100.00€ -
67CONTINGENCY FOR CHANGING VENT COVERS0 M2 € 20.00€ -
68BACKGROUND VENT0 Nr € 55.75€ -
69PERMANENT VENT0 Nr € 65.00€ -
70MECHANICAL VENT (INCLUDING CORE)0 Nr € 200.00€ -
71NEW CHARCOAL EXTRACTOR0 Nr € 250.00€ -
72CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD0 Nr € 60.00€ -
73SCAFFOLDING0€600.00€ -
74MISCELLANEOUS ITEM - OVERWRITE0€ -
75Total Base Cost: € -
76Unit Base Cost:
Sheet2
Cell Formulas
RangeFormula
C65:C74,C52:C60,C37:C47,C19:C32,C10:C13C10=SUM($H$4*H10+$I$4*I10+$J$4*J10+$K$4*K10+$L$4*L10+$M$4*M10+$N$4*N10+$O$4*O10)
B15B15=IFERROR(((#REF!)+(#REF!*$G$5)+(#REF!*0.07*0.65)),"")
F65:F74,F52:F60,F37:F47,F19:F32,F10:F13F10=E10*C10
F14F14=SUM(F10:F13)
F15F15=IFERROR((F14/E9),"")
F33F33=SUM(F19:F32)
F34F34=IFERROR((F33/E18),"")
F48F48=SUM(F37:F47)
F49F49=IFERROR((F48/E36),"")
F61F61=SUM(F52:F60)
F62F62=IFERROR((F61/E51),"")
F75F75=SUM(F65:F74)
F76F76=IFERROR((F75/E64),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H64:O64Cell Value="No"textNO
H64:O64Cell Value="Yes"textNO
H64:O64Cell Value="Not Sure"textNO
H51:O51Cell Value="No"textNO
H51:O51Cell Value="Yes"textNO
H51:O51Cell Value="Not Sure"textNO
H36:O36Cell Value="No"textNO
H36:O36Cell Value="Yes"textNO
H36:O36Cell Value="Not Sure"textNO
H18:O18Cell Value="No"textNO
H18:O18Cell Value="Yes"textNO
H18:O18Cell Value="Not Sure"textNO
L9:O9Cell Value="No"textNO
L9:O9Cell Value="Yes"textNO
L9:O9Cell Value="Not Sure"textNO
H9:J9Cell Value="No"textNO
H9:J9Cell Value="Yes"textNO
H9:J9Cell Value="Not Sure"textNO
K9Cell Value="No"textNO
K9Cell Value="Yes"textNO
K9Cell Value="Not Sure"textNO
C9Cell Value="No"textNO
C9Cell Value="Yes"textNO
C9Cell Value="Not Sure"textNO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174Cell Value="No"textNO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174Cell Value="Yes"textNO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174Cell Value="Not Sure"textNO
Cells with Data Validation
CellAllowCriteria
H5:O5List=House_Type
H6:O6List=House_Or_Apartment
H51:O51List=Required
H9:O9List=Required
H18:O18List=Required
H64:O64List=Required
H36:O36List=Required
C9List=Sheet3!$B$2:$B$3
C18List=Sheet3!$B$2:$B$3
C36List=Sheet3!$B$2:$B$3
C51List=Sheet3!$B$2:$B$3
C64List=Sheet3!$B$2:$B$3
 

foleycormac

New Member
Joined
Apr 9, 2015
Messages
15
Office Version
  1. 2019

ADVERTISEMENT

Test Worksheet.xlsm
BCDEFGHIJKLMNO
2ITEM Total Quantity All Estates UNIT UNIT BASE COST TOTAL BASE COST TypeTypeTypeTypeTypeTypeTypeType
3TypeABCDEFGH
4Quantity1043
5Unit Type Semi-D 2 StoreyMid Terrace 2 StoreySemi-D 2 Storey
6Select House Or ApartmentHouseHouseHouseHouseHouseHouseHouseHouse
73 Bed3 Bed4 Bed
8Required?Required?Required?Required?Required?Required?Required?Required?Required?
9FLOOR INSULATIONYes Total No. of Units NoNoNoNoNoNoNoNo
10Excavate & build back up floor including XTRATHERM THIN-R XT/UF 160mm (2 LAYERS OF 80MM) POLYISO (PIR) U-Value 0.146 W/m2K0 m2 € -
11Supply Insulation 2 layers of 80mm Xtratherm XT/UF 2 layers of 80mm XT/UF (€28/sheet) & 25mm XT/UF perimeter (€16/sheet)0 m2 € -
12Contingency0 m2 € -
13MISCELLANEOUS ITEM - OVERWRITE0 m2 € -
14Total Base Cost: € -
15 Unit Base Cost:
16
17Required?Required?Required?Required?Required?Required?Required?Required?Required?
18ATTIC INSULATIONYes Total No. of Units 8NoNoNoNoNoNoNoNo
19EARTHWOOL 200MM 0 m2 € 15.00€ -
20TANK JACKET (SMALL)0 m2 € 30.00€ -
21TANK JACKET (LARGE)0 m2 € 30.00€ -
22PIPE LAGGING ATTIC 0 m2 € 6.00€ -
23LOFT HATCH (kingspan 100mm retro draft proofing and hooks and eyes0 Nr € 40.00€ -
24WALKWAY SUPPLY & FIT 0 Nr € 30.00€ -
25REMOVE, RAISE % REINSTALL ATTIC SECTION OF FLOORING0 Nr € 38.44€ -
26MECHANICAL FAN INCLUDING INSULATED FLEXI & SLATE VENT0 Nr € 320.00€ -
27INSULATED FLEXI FOR EXISTING BATHROOM MEV0 Nr € 50.00€ -
28ROOF VENT 20,000mm20 Nr € 75.00€ -
29SOFFIT VENTS 2,500mm20 Nr € 3.50€ -
30HLI0 m2 €83.33€ -
31MISCELLANEOUS ITEM - OVERWRITE0 m2 €100.00€ -
32MISCELLANEOUS ITEM - OVERWRITE0 m2 €100.00€ -
33Total Base Cost: € -
34Unit Base Cost: € -
35Required?Required?Required?Required?Required?Required?Required?Required?Required?
36CAVITY WALL INSULATIONNo Total No. of Units NoNoNoNoNoNoNoNo
37CAVITY WALL INSULATION 50MM 0 m2 € 15.00€ -
38CAVITY WALL INSULATION 75MM 0 m2 € 5.50€ -
39CAVITY WALL INSULATION 100MM 0 m2 € 5.50€ -
40CONTINGENCY FOR CHANGING VENT COVERS0 M2 € 20.00€ -
41BACKGROUND VENT0 Nr € 55.75€ -
42PERMANENT VENT0 Nr € 65.00€ -
43MECHANICAL VENT (INCLUDING CORE)0 Nr € 250.00€ -
44NEW CHARCOAL EXTRACTOR0 Nr € 250.00€ -
45CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD0 Nr € 80.00€ -
46MISCELLANEOUS ITEM - OVERWRITE0€ -
47MISCELLANEOUS ITEM - OVERWRITE0€ -
48Total Base Cost: € -
49Unit Base Cost:
50Required?Required?Required?Required?Required?Required?Required?Required?Required?
51INTERNAL WALL INSULATIONNo Total No. of Units NoNoNoNoNoNoNoNo
52INTERNAL WALL INSULATION 82.5MM 0 m2 € 75.00€ -
53CONTINGENCY FOR CHANGING VENT COVERS0 M2 € 20.00€ -
54BACKGROUND VENT0 Nr € 55.75€ -
55PERMANENT VENT0 Nr € 65.00€ -
56MECHANICAL VENT (INCLUDING CORE)0 Nr € 200.00€ -
57NEW CHARCOAL EXTRACTOR0 Nr € 250.00€ -
58CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD0 Nr € 60.00€ -
59MISCELLANEOUS ITEM - OVERWRITE0€ -
60MISCELLANEOUS ITEM - OVERWRITE0€ -
61Total Base Cost: € -
62Unit Base Cost:
63Required?Required?Required?Required?Required?Required?Required?Required?Required?
64EXTERNAL WALL INSULATIONNo Total No. of Units NoNoNoNoNoNoNoNo
65EXTERNAL WALL INSULATION LESS THAN 65M20 m2 € 150.00€ -
66EXTERNAL WALL INSULATION GREATER THAN 65M20 m2 € 100.00€ -
67CONTINGENCY FOR CHANGING VENT COVERS0 M2 € 20.00€ -
68BACKGROUND VENT0 Nr € 55.75€ -
69PERMANENT VENT0 Nr € 65.00€ -
70MECHANICAL VENT (INCLUDING CORE)0 Nr € 200.00€ -
71NEW CHARCOAL EXTRACTOR0 Nr € 250.00€ -
72CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD0 Nr € 60.00€ -
73SCAFFOLDING0€600.00€ -
74MISCELLANEOUS ITEM - OVERWRITE0€ -
75Total Base Cost: € -
76Unit Base Cost:
Sheet2
Cell Formulas
RangeFormula
C65:C74,C52:C60,C37:C47,C19:C32,C10:C13C10=SUM($H$4*H10+$I$4*I10+$J$4*J10+$K$4*K10+$L$4*L10+$M$4*M10+$N$4*N10+$O$4*O10)
B15B15=IFERROR(((#REF!)+(#REF!*$G$5)+(#REF!*0.07*0.65)),"")
F65:F74,F52:F60,F37:F47,F19:F32,F10:F13F10=E10*C10
F14F14=SUM(F10:F13)
F15F15=IFERROR((F14/E9),"")
F33F33=SUM(F19:F32)
F34F34=IFERROR((F33/E18),"")
F48F48=SUM(F37:F47)
F49F49=IFERROR((F48/E36),"")
F61F61=SUM(F52:F60)
F62F62=IFERROR((F61/E51),"")
F75F75=SUM(F65:F74)
F76F76=IFERROR((F75/E64),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H64:O64Cell Value="No"textNO
H64:O64Cell Value="Yes"textNO
H64:O64Cell Value="Not Sure"textNO
H51:O51Cell Value="No"textNO
H51:O51Cell Value="Yes"textNO
H51:O51Cell Value="Not Sure"textNO
H36:O36Cell Value="No"textNO
H36:O36Cell Value="Yes"textNO
H36:O36Cell Value="Not Sure"textNO
H18:O18Cell Value="No"textNO
H18:O18Cell Value="Yes"textNO
H18:O18Cell Value="Not Sure"textNO
L9:O9Cell Value="No"textNO
L9:O9Cell Value="Yes"textNO
L9:O9Cell Value="Not Sure"textNO
H9:J9Cell Value="No"textNO
H9:J9Cell Value="Yes"textNO
H9:J9Cell Value="Not Sure"textNO
K9Cell Value="No"textNO
K9Cell Value="Yes"textNO
K9Cell Value="Not Sure"textNO
C9Cell Value="No"textNO
C9Cell Value="Yes"textNO
C9Cell Value="Not Sure"textNO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174Cell Value="No"textNO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174Cell Value="Yes"textNO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174Cell Value="Not Sure"textNO
Cells with Data Validation
CellAllowCriteria
H5:O5List=House_Type
H6:O6List=House_Or_Apartment
H51:O51List=Required
H9:O9List=Required
H18:O18List=Required
H64:O64List=Required
H36:O36List=Required
C9List=Sheet3!$B$2:$B$3
C18List=Sheet3!$B$2:$B$3
C36List=Sheet3!$B$2:$B$3
C51List=Sheet3!$B$2:$B$3
C64List=Sheet3!$B$2:$B$3
There are maybe 12-13 different types of upgrade works that I need to price.
This is just an extract from the overall workbook.
I want to be able to hide the elements that are not relevant to a project to have it look less cluttered.
Also, I would like to hide columns H:O depending on how many different house types are in a project. In this example there are 3 (A, B, C in cells H4, I4 & J4 respectively), so I would like to hide columns K:O.

I hope I'm explaining myself correctly.
Thanks again for the help.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
I don't need to see the spreadsheet or formulas. I am just looking for an explanation, in plain English, of your first couple of conditions, like you did in your original example:
For example, if C12 is "No" then rows 13:18 are hidden.
If C20 is "No" then rows 21:36 are hidden.
So, you gave me two there. Can you give me a few more?
 

foleycormac

New Member
Joined
Apr 9, 2015
Messages
15
Office Version
  1. 2019
I don't need to see the spreadsheet or formulas. I am just looking for an explanation, in plain English, of your first couple of conditions, like you did in your original example:

So, you gave me two there. Can you give me a few more?
sure:

ideally, I'd like to have a front page where I can enter details about the project.
On this worksheet (lets call it Details) I would like to have a list of 15 measures that could be included for the project. Each one would have a yes/no option on a drop down list.
All of the pricing information for the measures would be on a separate worksheet (Pricing).
On the Details sheet, If you select yes, the relevant rows on the Pricing sheet would remain visible.
If you select no, the relevant rows would be hidden.

At the moment the way I have set it up is as follows:
Details sheet cell B21 - relevant cells to hide/unhide = Pricing sheet rows 8:15
Details sheet cell B22 - relevant cells to hide/unhide = Pricing sheet rows 16:33
Details sheet cell B23 - relevant cells to hide/unhide = Pricing sheet rows 34:48
Details sheet cell B24 - relevant cells to hide/unhide = Pricing sheet rows 49:61
Details sheet cell B25 - relevant cells to hide/unhide = Pricing sheet rows 62:75
Details sheet cell B26 - relevant cells to hide/unhide = Pricing sheet rows 76:98
Details sheet cell B27 - relevant cells to hide/unhide = Pricing sheet rows 99:120
Details sheet cell B28 - relevant cells to hide/unhide = Pricing sheet rows 121:126
Details sheet cell B29 - relevant cells to hide/unhide = Pricing sheet rows 127:139
Details sheet cell B30 - relevant cells to hide/unhide = Pricing sheet rows 140:147
Details sheet cell B31 - relevant cells to hide/unhide = Pricing sheet rows 148:154
Details sheet cell B32 - relevant cells to hide/unhide = Pricing sheet rows 155:160
Details sheet cell B33 - relevant cells to hide/unhide = Pricing sheet rows 161:166

Also, on the Details sheet, I would like to have a cell where you can enter the number of different house types the project has. When you enter the number, this would then show that amount of the relevant columns on the Pricing sheet.
The cell that you enter the number of types is on Details H2
The columns that would be hidden/unhidden would be as follows:
1 type - Pricing sheet, hide columns K:S
2 types - Pricing sheet, hide columns L:S
3 types - Pricing sheet, hide columns M:S
4 types - Pricing sheet, hide columns N:S
5 types - Pricing sheet, hide columns O:S
6 types - Pricing sheet, hide columns P:S
7 types - Pricing sheet, hide columns Q:S
8 types - Pricing sheet, hide columns R:S
9 types - Pricing sheet, hide columns S

Hope that makes sense
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
OK, assuming that cell H2 allows numeric entries only from 1 to 9 (which you can control with Data Validation), put this code on your Details sheet:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rws As String
    Dim cls As String

'   Only run if one cell updated
    If Target.CountLarge > 1 Then Exit Sub

'   See what cell was update
    Select Case Target.Address

'       Check cell H2
        Case "$H$2"
'           First unhide all columns
            Sheets("Pricing").Columns("K:S").EntireColumn.Hidden = False
'           Get first column to hide, depending on value in H2
            cls = Chr(74 + Target.Value)
'           Hide columns on pricing sheet
            Sheets("Pricing").Columns(cls & ":S").EntireColumn.Hidden = True
            
'       Check to see if B21 was updated
        Case "$B$21"
            rws = "8:15"
'       Check to see if B22 was updated
        Case "$B$22"
            rws = "16:33"
'       Check to see if B23 was updated
        Case "$B$23"
            rws = "34:48"
'       continue on for cells B24-B32
'       ...
'           ...
'       Check to see if B23 was updated
        Case "$B$33"
            rws = "161:166"
    End Select
    
'   Check to see if rws updated
    If rws <> "" Then
        Select Case Target.Value
            Case "Yes"
                Sheets("Pricing").Rows(rws).EntireRow.Hidden = False
            Case "No"
                Sheets("Pricing").Rows(rws).EntireRow.Hidden = True
        End Select
    End If
                    
End Sub
Note that since there is no distinct pattern in how many rows to hide, you will need a separate Case clause for each cell in B21:B33. I did B21-B23 and B33 for you. You can fill in the rest. Just follow the same method I did for the first three. The spots with the "..." is where you will want to insert that.
 
Solution

Forum statistics

Threads
1,141,303
Messages
5,705,590
Members
421,400
Latest member
chakam

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
Top