Hiding rows based on the value of data in a column

[Cliffork]

I use an excel sheet to take data on instruments I calibrate. There are three different types of instruments that I calibrate, so I have certain denominators that tell them apart, and thus tell what lines I need to test for this particular unit.

I'm trying to make it so that cell V3 reads if the instruments serial number contains a D, and if it does, I want it to automatically hide rows 43-48 (and others once I'm done creating the workflow)

I'm trying to do this by making V43-V48 show 0 if V3 is FALSE, and 1 if TRUE. I have code that will hide all rows with a 0 in column V.

The problem I'm having now though, is that the code seems to have made undo/redo unusable, which is pretty annoying. The row hiding works fine though.

I would like to either A. fix the problem of not being able to undo anymore, or write new code to meet the same goal in a different way. Suggestions?

Here is my VBA code and a mini-sheet.

Private Sub Worksheet_Calculate()
'hides/unhides HCH gases from the final inspection sheet. V3 is true if it is an HCH
Dim c As Range
Application.EnableEvents = False
For Each c In Range("V1:V200")

If c.Value = "0" Then
Rows(c.Row & ":" & c.Row).EntireRow.Hidden = True
Else
Range(c.Row & ":" & c.Row).EntireRow.Hidden = False
End If

Next
Application.EnableEvents = True
End Sub

cal form test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Envision Final Inspection and Repairs Checklist
2	Date:																				Barometric?	TRUE
3	SN:	d																			D Unit?	TRUE
4	RMA:	0500-
5	Tech:
6
7
8	Yes	No	General Inspection Points
9			Battery life indicator working?			Number of Battery Bars:
10			Does the unit appear to charge when plugged in?
11			Has the firmware been upgraded to 1.31?
12			Have the PCB screws been tightened down?
13			Does the bluetooth connection work with a Nomad?
14			Is the Luer partical filter in good condition?
15
16	Yes	No	Pressure Related Inspection Points
17			Does the entire meter pass a pressure test?
18
19			Pressure Inspection
20			Please check the pressure readings in the Pressure Calibration screen.
21
22			Zeroes					Pressure Readings
23				Low	High				Low	High
24			System					System
25			Static					Static
26			Differental					Differential
27	Yes	No
28			Do all of the pressure sensors read correctly and hold a vacuum?
29			Does the Impact Port hold a vacuum?
30	X		Does the Barometric Pressure Sensor read correctly at room pressure?							ENV		Testo		difference
31										''Hg	mbar	mbar	''Hg	0.0
32		Gas Readings									0.0		0.0
33		Please check gas qualities with the meter in the analyze screen.
34				CH4 %	CO2 %	O2 %	Bal %
35	20.9 O2 Reading						100
36	99.99 N2 Reading						100
37	50/35% Reading						100
38	15/15 Reading						100
39	40/60 Reading						100
40	100 CO2 Reading						100
43																						1
44				H2 %	CO ppm	H2S ppm	Bal %															1
45	2% H2 Reading						100															1
46	50% H2 Reading						100															1
47	1500ppm CO Reading						100															1
48	500ppm H2S Reading						100															1
Final Insp.

Cell Formulas
Range	Formula
V2	V2	=OR(IF(ISNUMBER(SEARCH("B", B3)), TRUE, FALSE),IF(ISNUMBER(SEARCH("D", B3)), TRUE, FALSE))
V3	V3	=IF(ISNUMBER(SEARCH("D",B3)),TRUE,FALSE)
B3	B3	=('Customer Info'!B15)
B4	B4	=('Customer Info'!B13)
A30	A30	=IF(AND(N31>-2,N31<2),"X","")
B30	B30	=IF(AND(N31>-2,N31<2),"","X")
C30	C30	=IF(ISNUMBER(SEARCH("TRUE",V2)),"Does the Barometric Pressure Sensor read correctly at room pressure?", "")
J30	J30	=IF(ISNUMBER(SEARCH("TRUE",V2)),"ENV", "")
J31	J31	=IF(ISNUMBER(SEARCH("TRUE",V2)),"''Hg", "")
L30	L30	=IF(ISNUMBER(SEARCH("TRUE",V2)),"Testo", "")
L31	L31	=IF(ISNUMBER(SEARCH("TRUE",V2)),"mbar", "")
N30	N30	=IF(ISNUMBER(SEARCH("TRUE",V2)),"difference", "")
N31	N31	=IF(ISNUMBER(SEARCH("TRUE",V2)),L32-(K32-J33), "")
K31	K31	=IF(ISNUMBER(SEARCH("TRUE",V2)),"mbar", "")
K32	K32	=IF(ISNUMBER(SEARCH("TRUE",V2)),J32*33.8639,"")
M31	M31	=IF(ISNUMBER(SEARCH("TRUE",V2)),"''Hg", "")
M32	M32	=IF(ISNUMBER(SEARCH("TRUE",V2)),L32/33.8639, "")
G35:G40,G45:G48	G35	=100-SUM(D35:F35)
V43	V43	=IF(V3=TRUE,1,0)
V44	V44	=IF(V3=TRUE,1,0)
V45	V45	=IF(V3=TRUE,1,0)
V46	V46	=IF(V3=TRUE,1,0)
V47	V47	=IF(V3=TRUE,1,0)
V48	V48	=IF(V3=TRUE,1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B30	Expression	=$V$2=FALSE	text	NO
D48	Cell	contains a blank value	text	NO
D48	Cell Value	between 95 and 105	text	NO
D48	Cell Value	<95	text	NO
D48	Cell Value	>105	text	NO
E48	Cell	contains a blank value	text	NO
E48	Cell Value	between 0 and 0.1	text	NO
E48	Cell Value	>0.02	text	NO
E48	Cell Value	<-0.02	text	NO
D47	Cell	contains a blank value	text	NO
D47	Cell Value	between 0 and 0.1	text	NO
D47	Cell Value	>0.02	text	NO
D47	Cell Value	<-0.02	text	NO
F46:F48	Cell Value	>0.1	text	NO
F46:F48	Cell	contains a blank value	text	NO
F46:F48	Cell Value	between 0 and 0.1	text	NO
F45	Cell Value	>0.1	text	NO
D45:F45	Cell	contains a blank value	text	NO
F45	Cell Value	between 0 and 0.1	text	NO
D45:E45	Cell Value	>16.5	text	NO
D45:E45	Cell Value	<13.5	text	NO
D45:E45	Cell Value	between 13.5 and 16.5	text	NO
D46:E46,E47	Cell	contains a blank value	text	NO
E47	Cell Value	between 95 and 105	text	NO
E47	Cell Value	<95	text	NO
E46	Cell Value	<57	text	NO
E46	Cell Value	>63	text	NO
D46	Cell Value	<37	text	NO
D46	Cell Value	>43	text	NO
E47	Cell Value	>105	text	NO
E46	Cell Value	between 57 and 63	text	NO
D46	Cell Value	between 37 and 43	text	NO
D40	Cell	contains a blank value	text	NO
D40	Cell Value	between 0 and 0.1	text	NO
D40	Cell Value	>0.1	text	NO
D40	Cell Value	<0	text	NO
F39:F41	Cell	contains a blank value	text	NO
F40	Cell Value	>0.1	text	NO
F40	Cell Value	<-0.1	text	NO
F39	Cell Value	>0.1	text	NO
F39	Cell Value	<-0.1	text	NO
F39	Cell Value	<20.7	text	NO
F39	Cell Value	>21.1	text	NO
F39	Cell Value	between 20.7 and 21.1	text	NO
F40	Cell Value	between 0 and 0.1	text	NO
A30	Expression	=ISBLANK($J$32)	text	NO
B30	Expression	=$V$2=TRUE	text	NO
A30:B30	Expression	=$V$2=TRUE	text	NO
A30:B30	Expression	=$V$2=TRUE	text	NO
N30	Expression	=$V$2=TRUE	text	NO
L32,J32	Expression	=$V$2=TRUE	text	NO
J30:M32	Expression	=$V$2=TRUE	text	NO
B9	Expression	=IF($A$9:$B$9,"X","")	text	NO
N31	Cell	contains a blank value	text	NO
N31	Cell Value	=0	text	NO
N31	Cell Value	between -2 and 2	text	NO
N31	Cell Value	<-2	text	NO
D35:F38	Cell	contains a blank value	text	NO
F37	Cell Value	>0.1	text	NO
F37	Cell Value	<-0.1	text	NO
F36	Cell Value	>0.1	text	NO
F36	Cell Value	<-0.1	text	NO
D35:E36,F38,D36:F36	Cell Value	between 0 and 0.1	text	NO
D35:E36	Cell Value	>0.02	text	NO
F38	Cell Value	>0.1	text	NO
F38	Cell Value	<0	text	NO
E37	Cell Value	>35.3	text	NO
J24:J26,I25:I26,E24:E26,D25:D26	Cell	contains a blank value	text	NO
I26	Cell Value	>4.53	text	NO
I26	Cell Value	<4.47	text	NO
J26	Cell Value	>20.1	text	NO
J26	Cell Value	<19.9	text	NO
J26	Cell Value	between 19.9 and 20.1	text	NO
I26	Cell Value	between 4.47 and 4.53	text	NO
I25	Cell Value	<-4.53	text	NO
I25	Cell Value	>-4.47	text	NO
I25	Cell Value	between -4.47 and -4.53	text	NO
J24:J25	Cell Value	between -99 and -101	text	NO
D25:D26,E26	Cell Value	<-0.1	text	NO
D25:D26,E26	Cell Value	>0.1	text	NO
D25:D26,E26	Cell Value	between -0.1 and 0.1	text	NO
E24	Cell Value	between -0.65 and 0.65	text	NO
E25	Cell Value	between -0.65 and 0.65	text	NO
E24:E25	Cell Value	<-0.65	text	NO
E24:E25	Cell Value	>0.65	text	NO
D39:E39,D41,E40	Cell	contains a blank value	text	NO
E40	Cell Value	between 98 and 102	text	NO
E40	Cell Value	<98	text	NO
E39	Cell Value	<59.5	text	NO
E39	Cell Value	>60.5	text	NO
D39	Cell Value	<39.5	text	NO
D39	Cell Value	>40.5	text	NO
E40	Cell Value	>102	text	NO
E39	Cell Value	between 59.5 and 60.5	text	NO
E37	Cell Value	<34.7	text	NO
D38:E38	Cell Value	>15.3	text	NO
D38:E38	Cell Value	<14.7	text	NO
F35:F36	Cell Value	<20.7	text	NO
F35:F36	Cell Value	>21.1	text	NO
D37	Cell Value	<49.7	text	NO
D37	Cell Value	>50.3	text	NO
D38:E38	Cell Value	between 14.7 and 15.3	text	NO
F35:F36	Cell Value	between 20.7 and 21.1	text	NO
D35:E36	Cell Value	<-0.02	text	NO
F37	Cell Value	between 0 and 0.1	text	NO
E37	Cell Value	between 34.7 and 35.3	text	NO
D37	Cell Value	between 49.7 and 50.3	text	NO
J24:J25	Cell Value	<-101	text	NO
D39	Cell Value	between 39.5 and 40.5	text	NO
N31	Cell Value	>2	text	NO
J24:J25	Cell Value	>-99	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K32	Whole number	between (M32-K32)-2 and (M32-K32)+2
L32	Any value
D24:J26	Any value
B28:B30	Whole number	between 1 and 2
B17	Whole number	between 101 and 102
B9:B14	Whole number	between 101 and 102
I9	List	='Drop Down lists'!$D$1:$D$8
B5:C5	List	='Drop Down lists'!$A$1:$A$7

 

[Fluff]

Glad you sorted it & thanks for the feedback. Although that still clears the undo stack for me.
However you can simplify it like

Private Sub Worksheet_change(ByVal Target As Range)
' hides rows 43-48 on the final inspection page when V3 is false
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "V3" Then
   Sheets("Final Insp.").Rows("43:48").Hidden = Target.Value <> "TRUE"
End If

End Sub

 

[Cliffork]

Glad you sorted it & thanks for the feedback. Although that still clears the undo stack for me.
However you can simplify it like

Private Sub Worksheet_change(ByVal Target As Range)
' hides rows 43-48 on the final inspection page when V3 is false
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "V3" Then
   Sheets("Final Insp.").Rows("43:48").Hidden = Target.Value <> "TRUE"
End If

End Sub

I like the simplification of the code here, it would declutter a lot of my VBA if I can integrate it, however it doesn't appear to unhide the value in V3 changes again

 

[Fluff]

It should work as long as V3 is being changed manually, although you originally had it as a formula.

 

[Cliffork]

It should work as long as V3 is being changed manually, although you originally had it as a formula.

Is there any way to make it work as a formula? I can work around it if not, but the formula aspect makes things a lot easier.

 

[Fluff]

Yes but that would take you back to what you had before.

 

[Cliffork]

Yes but that would take you back to what you had before.

I'm just going to leave it with the extra text then, I was able to use what you wrote it shorten it slightly, but this one will unhide the cells if the value changes

If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "B15" Then
With Sheets("Final Insp.")
If Target.Value Like "*D" Then
.Rows("43:48").Hidden = True
Else
.Rows("43:48").Hidden = False
End If
End With
End If

 

[Fluff]

Glad to help & thanks for the feedback.