Hide/unhide different rows based on values in multiple cells

[foleycormac]

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"

 

[Joe4]

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]

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]

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.

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]

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.

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]

Test Worksheet.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	ITEM	Total Quantity All Estates	UNIT	UNIT BASE COST	TOTAL BASE COST		Type	Type	Type	Type	Type	Type	Type	Type
3				Type			A	B	C	D	E	F	G	H
4				Quantity			10	4	3
5				Unit Type			Semi-D 2 Storey	Mid Terrace 2 Storey	Semi-D 2 Storey
6				Select House Or Apartment			House	House	House	House	House	House	House	House
7							3 Bed	3 Bed	4 Bed
8		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
9	FLOOR INSULATION	Yes	Total No. of Units				No	No	No	No	No	No	No	No
10	Excavate & build back up floor including XTRATHERM THIN-R XT/UF 160mm (2 LAYERS OF 80MM) POLYISO (PIR) U-Value 0.146 W/m2K	0	m2		€ -
11	Supply Insulation 2 layers of 80mm Xtratherm XT/UF 2 layers of 80mm XT/UF (€28/sheet) & 25mm XT/UF perimeter (€16/sheet)	0	m2		€ -
12	Contingency	0	m2		€ -
13	MISCELLANEOUS ITEM - OVERWRITE	0	m2		€ -
14				Total Base Cost:	€ -
15				Unit Base Cost:
16
17		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
18	ATTIC INSULATION	Yes	Total No. of Units	8			No	No	No	No	No	No	No	No
19	EARTHWOOL 200MM	0	m2	€ 15.00	€ -
20	TANK JACKET (SMALL)	0	m2	€ 30.00	€ -
21	TANK JACKET (LARGE)	0	m2	€ 30.00	€ -
22	PIPE LAGGING ATTIC	0	m2	€ 6.00	€ -
23	LOFT HATCH (kingspan 100mm retro draft proofing and hooks and eyes	0	Nr	€ 40.00	€ -
24	WALKWAY SUPPLY & FIT	0	Nr	€ 30.00	€ -
25	REMOVE, RAISE % REINSTALL ATTIC SECTION OF FLOORING	0	Nr	€ 38.44	€ -
26	MECHANICAL FAN INCLUDING INSULATED FLEXI & SLATE VENT	0	Nr	€ 320.00	€ -
27	INSULATED FLEXI FOR EXISTING BATHROOM MEV	0	Nr	€ 50.00	€ -
28	ROOF VENT 20,000mm2	0	Nr	€ 75.00	€ -
29	SOFFIT VENTS 2,500mm2	0	Nr	€ 3.50	€ -
30	HLI	0	m2	€83.33	€ -
31	MISCELLANEOUS ITEM - OVERWRITE	0	m2	€100.00	€ -
32	MISCELLANEOUS ITEM - OVERWRITE	0	m2	€100.00	€ -
33				Total Base Cost:	€ -
34				Unit Base Cost:	€ -
35		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
36	CAVITY WALL INSULATION	No	Total No. of Units				No	No	No	No	No	No	No	No
37	CAVITY WALL INSULATION 50MM	0	m2	€ 15.00	€ -
38	CAVITY WALL INSULATION 75MM	0	m2	€ 5.50	€ -
39	CAVITY WALL INSULATION 100MM	0	m2	€ 5.50	€ -
40	CONTINGENCY FOR CHANGING VENT COVERS	0	M2	€ 20.00	€ -
41	BACKGROUND VENT	0	Nr	€ 55.75	€ -
42	PERMANENT VENT	0	Nr	€ 65.00	€ -
43	MECHANICAL VENT (INCLUDING CORE)	0	Nr	€ 250.00	€ -
44	NEW CHARCOAL EXTRACTOR	0	Nr	€ 250.00	€ -
45	CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD	0	Nr	€ 80.00	€ -
46	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
47	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
48				Total Base Cost:	€ -
49				Unit Base Cost:
50		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
51	INTERNAL WALL INSULATION	No	Total No. of Units				No	No	No	No	No	No	No	No
52	INTERNAL WALL INSULATION 82.5MM	0	m2	€ 75.00	€ -
53	CONTINGENCY FOR CHANGING VENT COVERS	0	M2	€ 20.00	€ -
54	BACKGROUND VENT	0	Nr	€ 55.75	€ -
55	PERMANENT VENT	0	Nr	€ 65.00	€ -
56	MECHANICAL VENT (INCLUDING CORE)	0	Nr	€ 200.00	€ -
57	NEW CHARCOAL EXTRACTOR	0	Nr	€ 250.00	€ -
58	CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD	0	Nr	€ 60.00	€ -
59	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
60	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
61				Total Base Cost:	€ -
62				Unit Base Cost:
63		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
64	EXTERNAL WALL INSULATION	No	Total No. of Units				No	No	No	No	No	No	No	No
65	EXTERNAL WALL INSULATION LESS THAN 65M2	0	m2	€ 150.00	€ -
66	EXTERNAL WALL INSULATION GREATER THAN 65M2	0	m2	€ 100.00	€ -
67	CONTINGENCY FOR CHANGING VENT COVERS	0	M2	€ 20.00	€ -
68	BACKGROUND VENT	0	Nr	€ 55.75	€ -
69	PERMANENT VENT	0	Nr	€ 65.00	€ -
70	MECHANICAL VENT (INCLUDING CORE)	0	Nr	€ 200.00	€ -
71	NEW CHARCOAL EXTRACTOR	0	Nr	€ 250.00	€ -
72	CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD	0	Nr	€ 60.00	€ -
73	SCAFFOLDING	0		€600.00	€ -
74	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
75				Total Base Cost:	€ -
76				Unit Base Cost:
Sheet2

Cell Formulas
Range	Formula
C65:C74,C52:C60,C37:C47,C19:C32,C10:C13	C10	=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)
B15	B15	=IFERROR(((#REF!)+(#REF!*$G$5)+(#REF!*0.07*0.65)),"")
F65:F74,F52:F60,F37:F47,F19:F32,F10:F13	F10	=E10*C10
F14	F14	=SUM(F10:F13)
F15	F15	=IFERROR((F14/E9),"")
F33	F33	=SUM(F19:F32)
F34	F34	=IFERROR((F33/E18),"")
F48	F48	=SUM(F37:F47)
F49	F49	=IFERROR((F48/E36),"")
F61	F61	=SUM(F52:F60)
F62	F62	=IFERROR((F61/E51),"")
F75	F75	=SUM(F65:F74)
F76	F76	=IFERROR((F75/E64),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H64:O64	Cell Value	="No"	text	NO
H64:O64	Cell Value	="Yes"	text	NO
H64:O64	Cell Value	="Not Sure"	text	NO
H51:O51	Cell Value	="No"	text	NO
H51:O51	Cell Value	="Yes"	text	NO
H51:O51	Cell Value	="Not Sure"	text	NO
H36:O36	Cell Value	="No"	text	NO
H36:O36	Cell Value	="Yes"	text	NO
H36:O36	Cell Value	="Not Sure"	text	NO
H18:O18	Cell Value	="No"	text	NO
H18:O18	Cell Value	="Yes"	text	NO
H18:O18	Cell Value	="Not Sure"	text	NO
L9:O9	Cell Value	="No"	text	NO
L9:O9	Cell Value	="Yes"	text	NO
L9:O9	Cell Value	="Not Sure"	text	NO
H9:J9	Cell Value	="No"	text	NO
H9:J9	Cell Value	="Yes"	text	NO
H9:J9	Cell Value	="Not Sure"	text	NO
K9	Cell Value	="No"	text	NO
K9	Cell Value	="Yes"	text	NO
K9	Cell Value	="Not Sure"	text	NO
C9	Cell Value	="No"	text	NO
C9	Cell Value	="Yes"	text	NO
C9	Cell Value	="Not Sure"	text	NO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174	Cell Value	="No"	text	NO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174	Cell Value	="Yes"	text	NO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174	Cell Value	="Not Sure"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H5:O5	List	=House_Type
H6:O6	List	=House_Or_Apartment
H51:O51	List	=Required
H9:O9	List	=Required
H18:O18	List	=Required
H64:O64	List	=Required
H36:O36	List	=Required
C9	List	=Sheet3!$B$2:$B$3
C18	List	=Sheet3!$B$2:$B$3
C36	List	=Sheet3!$B$2:$B$3
C51	List	=Sheet3!$B$2:$B$3
C64	List	=Sheet3!$B$2:$B$3

 

[foleycormac]

Test Worksheet.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	ITEM	Total Quantity All Estates	UNIT	UNIT BASE COST	TOTAL BASE COST		Type	Type	Type	Type	Type	Type	Type	Type
3				Type			A	B	C	D	E	F	G	H
4				Quantity			10	4	3
5				Unit Type			Semi-D 2 Storey	Mid Terrace 2 Storey	Semi-D 2 Storey
6				Select House Or Apartment			House	House	House	House	House	House	House	House
7							3 Bed	3 Bed	4 Bed
8		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
9	FLOOR INSULATION	Yes	Total No. of Units				No	No	No	No	No	No	No	No
10	Excavate & build back up floor including XTRATHERM THIN-R XT/UF 160mm (2 LAYERS OF 80MM) POLYISO (PIR) U-Value 0.146 W/m2K	0	m2		€ -
11	Supply Insulation 2 layers of 80mm Xtratherm XT/UF 2 layers of 80mm XT/UF (€28/sheet) & 25mm XT/UF perimeter (€16/sheet)	0	m2		€ -
12	Contingency	0	m2		€ -
13	MISCELLANEOUS ITEM - OVERWRITE	0	m2		€ -
14				Total Base Cost:	€ -
15				Unit Base Cost:
16
17		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
18	ATTIC INSULATION	Yes	Total No. of Units	8			No	No	No	No	No	No	No	No
19	EARTHWOOL 200MM	0	m2	€ 15.00	€ -
20	TANK JACKET (SMALL)	0	m2	€ 30.00	€ -
21	TANK JACKET (LARGE)	0	m2	€ 30.00	€ -
22	PIPE LAGGING ATTIC	0	m2	€ 6.00	€ -
23	LOFT HATCH (kingspan 100mm retro draft proofing and hooks and eyes	0	Nr	€ 40.00	€ -
24	WALKWAY SUPPLY & FIT	0	Nr	€ 30.00	€ -
25	REMOVE, RAISE % REINSTALL ATTIC SECTION OF FLOORING	0	Nr	€ 38.44	€ -
26	MECHANICAL FAN INCLUDING INSULATED FLEXI & SLATE VENT	0	Nr	€ 320.00	€ -
27	INSULATED FLEXI FOR EXISTING BATHROOM MEV	0	Nr	€ 50.00	€ -
28	ROOF VENT 20,000mm2	0	Nr	€ 75.00	€ -
29	SOFFIT VENTS 2,500mm2	0	Nr	€ 3.50	€ -
30	HLI	0	m2	€83.33	€ -
31	MISCELLANEOUS ITEM - OVERWRITE	0	m2	€100.00	€ -
32	MISCELLANEOUS ITEM - OVERWRITE	0	m2	€100.00	€ -
33				Total Base Cost:	€ -
34				Unit Base Cost:	€ -
35		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
36	CAVITY WALL INSULATION	No	Total No. of Units				No	No	No	No	No	No	No	No
37	CAVITY WALL INSULATION 50MM	0	m2	€ 15.00	€ -
38	CAVITY WALL INSULATION 75MM	0	m2	€ 5.50	€ -
39	CAVITY WALL INSULATION 100MM	0	m2	€ 5.50	€ -
40	CONTINGENCY FOR CHANGING VENT COVERS	0	M2	€ 20.00	€ -
41	BACKGROUND VENT	0	Nr	€ 55.75	€ -
42	PERMANENT VENT	0	Nr	€ 65.00	€ -
43	MECHANICAL VENT (INCLUDING CORE)	0	Nr	€ 250.00	€ -
44	NEW CHARCOAL EXTRACTOR	0	Nr	€ 250.00	€ -
45	CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD	0	Nr	€ 80.00	€ -
46	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
47	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
48				Total Base Cost:	€ -
49				Unit Base Cost:
50		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
51	INTERNAL WALL INSULATION	No	Total No. of Units				No	No	No	No	No	No	No	No
52	INTERNAL WALL INSULATION 82.5MM	0	m2	€ 75.00	€ -
53	CONTINGENCY FOR CHANGING VENT COVERS	0	M2	€ 20.00	€ -
54	BACKGROUND VENT	0	Nr	€ 55.75	€ -
55	PERMANENT VENT	0	Nr	€ 65.00	€ -
56	MECHANICAL VENT (INCLUDING CORE)	0	Nr	€ 200.00	€ -
57	NEW CHARCOAL EXTRACTOR	0	Nr	€ 250.00	€ -
58	CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD	0	Nr	€ 60.00	€ -
59	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
60	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
61				Total Base Cost:	€ -
62				Unit Base Cost:
63		Required?					Required?	Required?	Required?	Required?	Required?	Required?	Required?	Required?
64	EXTERNAL WALL INSULATION	No	Total No. of Units				No	No	No	No	No	No	No	No
65	EXTERNAL WALL INSULATION LESS THAN 65M2	0	m2	€ 150.00	€ -
66	EXTERNAL WALL INSULATION GREATER THAN 65M2	0	m2	€ 100.00	€ -
67	CONTINGENCY FOR CHANGING VENT COVERS	0	M2	€ 20.00	€ -
68	BACKGROUND VENT	0	Nr	€ 55.75	€ -
69	PERMANENT VENT	0	Nr	€ 65.00	€ -
70	MECHANICAL VENT (INCLUDING CORE)	0	Nr	€ 200.00	€ -
71	NEW CHARCOAL EXTRACTOR	0	Nr	€ 250.00	€ -
72	CONNECT EXISTING RECIRCULATING KITCHEN COOKER HOOD	0	Nr	€ 60.00	€ -
73	SCAFFOLDING	0		€600.00	€ -
74	MISCELLANEOUS ITEM - OVERWRITE	0			€ -
75				Total Base Cost:	€ -
76				Unit Base Cost:
Sheet2

Cell Formulas
Range	Formula
C65:C74,C52:C60,C37:C47,C19:C32,C10:C13	C10	=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)
B15	B15	=IFERROR(((#REF!)+(#REF!*$G$5)+(#REF!*0.07*0.65)),"")
F65:F74,F52:F60,F37:F47,F19:F32,F10:F13	F10	=E10*C10
F14	F14	=SUM(F10:F13)
F15	F15	=IFERROR((F14/E9),"")
F33	F33	=SUM(F19:F32)
F34	F34	=IFERROR((F33/E18),"")
F48	F48	=SUM(F37:F47)
F49	F49	=IFERROR((F48/E36),"")
F61	F61	=SUM(F52:F60)
F62	F62	=IFERROR((F61/E51),"")
F75	F75	=SUM(F65:F74)
F76	F76	=IFERROR((F75/E64),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H64:O64	Cell Value	="No"	text	NO
H64:O64	Cell Value	="Yes"	text	NO
H64:O64	Cell Value	="Not Sure"	text	NO
H51:O51	Cell Value	="No"	text	NO
H51:O51	Cell Value	="Yes"	text	NO
H51:O51	Cell Value	="Not Sure"	text	NO
H36:O36	Cell Value	="No"	text	NO
H36:O36	Cell Value	="Yes"	text	NO
H36:O36	Cell Value	="Not Sure"	text	NO
H18:O18	Cell Value	="No"	text	NO
H18:O18	Cell Value	="Yes"	text	NO
H18:O18	Cell Value	="Not Sure"	text	NO
L9:O9	Cell Value	="No"	text	NO
L9:O9	Cell Value	="Yes"	text	NO
L9:O9	Cell Value	="Not Sure"	text	NO
H9:J9	Cell Value	="No"	text	NO
H9:J9	Cell Value	="Yes"	text	NO
H9:J9	Cell Value	="Not Sure"	text	NO
K9	Cell Value	="No"	text	NO
K9	Cell Value	="Yes"	text	NO
K9	Cell Value	="Not Sure"	text	NO
C9	Cell Value	="No"	text	NO
C9	Cell Value	="Yes"	text	NO
C9	Cell Value	="Not Sure"	text	NO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174	Cell Value	="No"	text	NO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174	Cell Value	="Yes"	text	NO
C18,C36,C51,C64,C78,C123,C129,C142,C149,C154,C159,C167,C174	Cell Value	="Not Sure"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H5:O5	List	=House_Type
H6:O6	List	=House_Or_Apartment
H51:O51	List	=Required
H9:O9	List	=Required
H18:O18	List	=Required
H64:O64	List	=Required
H36:O36	List	=Required
C9	List	=Sheet3!$B$2:$B$3
C18	List	=Sheet3!$B$2:$B$3
C36	List	=Sheet3!$B$2:$B$3
C51	List	=Sheet3!$B$2:$B$3
C64	List	=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]

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]

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]

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:

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.