Hiding rows based on the value of data in a column

Cliffork

New Member
Joined
Feb 12, 2020
Messages
43
Office Version
  1. 365
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
ABCDEFGHIJKLMNOPQRSTUV
1Envision Final Inspection and Repairs Checklist
2Date:Barometric?TRUE
3SN:dD Unit?TRUE
4RMA:0500-
5Tech:
6
7
8YesNoGeneral Inspection Points
9Battery life indicator working? Number of Battery Bars:
10Does the unit appear to charge when plugged in?
11Has the firmware been upgraded to 1.31?
12Have the PCB screws been tightened down?
13Does the bluetooth connection work with a Nomad?
14Is the Luer partical filter in good condition?
15
16YesNoPressure Related Inspection Points
17Does the entire meter pass a pressure test?
18
19Pressure Inspection
20Please check the pressure readings in the Pressure Calibration screen.
21
22ZeroesPressure Readings
23LowHighLowHigh
24SystemSystem
25StaticStatic
26DifferentalDifferential
27YesNo
28Do all of the pressure sensors read correctly and hold a vacuum?
29Does the Impact Port hold a vacuum?
30X Does the Barometric Pressure Sensor read correctly at room pressure?ENVTestodifference
31''Hgmbarmbar''Hg0.0
32Gas Readings0.00.0
33Please check gas qualities with the meter in the analyze screen.
34CH4 %CO2 %O2 %Bal %
3520.9 O2 Reading 100
3699.99 N2 Reading 100
3750/35% Reading100
3815/15 Reading 100
3940/60 Reading100
40100 CO2 Reading100
431
44H2 %CO ppmH2S ppmBal %1
452% H2 Reading1001
4650% H2 Reading1001
471500ppm CO Reading1001
48500ppm H2S Reading1001
Final Insp.
Cell Formulas
RangeFormula
V2V2=OR(IF(ISNUMBER(SEARCH("B", B3)), TRUE, FALSE),IF(ISNUMBER(SEARCH("D", B3)), TRUE, FALSE))
V3V3=IF(ISNUMBER(SEARCH("D",B3)),TRUE,FALSE)
B3B3=('Customer Info'!B15)
B4B4=('Customer Info'!B13)
A30A30=IF(AND(N31>-2,N31<2),"X","")
B30B30=IF(AND(N31>-2,N31<2),"","X")
C30C30=IF(ISNUMBER(SEARCH("TRUE",V2)),"Does the Barometric Pressure Sensor read correctly at room pressure?", "")
J30J30=IF(ISNUMBER(SEARCH("TRUE",V2)),"ENV", "")
J31J31=IF(ISNUMBER(SEARCH("TRUE",V2)),"''Hg", "")
L30L30=IF(ISNUMBER(SEARCH("TRUE",V2)),"Testo", "")
L31L31=IF(ISNUMBER(SEARCH("TRUE",V2)),"mbar", "")
N30N30=IF(ISNUMBER(SEARCH("TRUE",V2)),"difference", "")
N31N31=IF(ISNUMBER(SEARCH("TRUE",V2)),L32-(K32-J33), "")
K31K31=IF(ISNUMBER(SEARCH("TRUE",V2)),"mbar", "")
K32K32=IF(ISNUMBER(SEARCH("TRUE",V2)),J32*33.8639,"")
M31M31=IF(ISNUMBER(SEARCH("TRUE",V2)),"''Hg", "")
M32M32=IF(ISNUMBER(SEARCH("TRUE",V2)),L32/33.8639, "")
G35:G40,G45:G48G35=100-SUM(D35:F35)
V43V43=IF(V3=TRUE,1,0)
V44V44=IF(V3=TRUE,1,0)
V45V45=IF(V3=TRUE,1,0)
V46V46=IF(V3=TRUE,1,0)
V47V47=IF(V3=TRUE,1,0)
V48V48=IF(V3=TRUE,1,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B30Expression=$V$2=FALSEtextNO
D48Cellcontains a blank value textNO
D48Cell Valuebetween 95 and 105textNO
D48Cell Value<95textNO
D48Cell Value>105textNO
E48Cellcontains a blank value textNO
E48Cell Valuebetween 0 and 0.1textNO
E48Cell Value>0.02textNO
E48Cell Value<-0.02textNO
D47Cellcontains a blank value textNO
D47Cell Valuebetween 0 and 0.1textNO
D47Cell Value>0.02textNO
D47Cell Value<-0.02textNO
F46:F48Cell Value>0.1textNO
F46:F48Cellcontains a blank value textNO
F46:F48Cell Valuebetween 0 and 0.1textNO
F45Cell Value>0.1textNO
D45:F45Cellcontains a blank value textNO
F45Cell Valuebetween 0 and 0.1textNO
D45:E45Cell Value>16.5textNO
D45:E45Cell Value<13.5textNO
D45:E45Cell Valuebetween 13.5 and 16.5textNO
D46:E46,E47Cellcontains a blank value textNO
E47Cell Valuebetween 95 and 105textNO
E47Cell Value<95textNO
E46Cell Value<57textNO
E46Cell Value>63textNO
D46Cell Value<37textNO
D46Cell Value>43textNO
E47Cell Value>105textNO
E46Cell Valuebetween 57 and 63textNO
D46Cell Valuebetween 37 and 43textNO
D40Cellcontains a blank value textNO
D40Cell Valuebetween 0 and 0.1textNO
D40Cell Value>0.1textNO
D40Cell Value<0textNO
F39:F41Cellcontains a blank value textNO
F40Cell Value>0.1textNO
F40Cell Value<-0.1textNO
F39Cell Value>0.1textNO
F39Cell Value<-0.1textNO
F39Cell Value<20.7textNO
F39Cell Value>21.1textNO
F39Cell Valuebetween 20.7 and 21.1textNO
F40Cell Valuebetween 0 and 0.1textNO
A30Expression=ISBLANK($J$32)textNO
B30Expression=$V$2=TRUEtextNO
A30:B30Expression=$V$2=TRUEtextNO
A30:B30Expression=$V$2=TRUEtextNO
N30Expression=$V$2=TRUEtextNO
L32,J32Expression=$V$2=TRUEtextNO
J30:M32Expression=$V$2=TRUEtextNO
B9Expression=IF($A$9:$B$9,"X","")textNO
N31Cellcontains a blank value textNO
N31Cell Value=0textNO
N31Cell Valuebetween -2 and 2textNO
N31Cell Value<-2textNO
D35:F38Cellcontains a blank value textNO
F37Cell Value>0.1textNO
F37Cell Value<-0.1textNO
F36Cell Value>0.1textNO
F36Cell Value<-0.1textNO
D35:E36,F38,D36:F36Cell Valuebetween 0 and 0.1textNO
D35:E36Cell Value>0.02textNO
F38Cell Value>0.1textNO
F38Cell Value<0textNO
E37Cell Value>35.3textNO
J24:J26,I25:I26,E24:E26,D25:D26Cellcontains a blank value textNO
I26Cell Value>4.53textNO
I26Cell Value<4.47textNO
J26Cell Value>20.1textNO
J26Cell Value<19.9textNO
J26Cell Valuebetween 19.9 and 20.1textNO
I26Cell Valuebetween 4.47 and 4.53textNO
I25Cell Value<-4.53textNO
I25Cell Value>-4.47textNO
I25Cell Valuebetween -4.47 and -4.53textNO
J24:J25Cell Valuebetween -99 and -101textNO
D25:D26,E26Cell Value<-0.1textNO
D25:D26,E26Cell Value>0.1textNO
D25:D26,E26Cell Valuebetween -0.1 and 0.1textNO
E24Cell Valuebetween -0.65 and 0.65textNO
E25Cell Valuebetween -0.65 and 0.65textNO
E24:E25Cell Value<-0.65textNO
E24:E25Cell Value>0.65textNO
D39:E39,D41,E40Cellcontains a blank value textNO
E40Cell Valuebetween 98 and 102textNO
E40Cell Value<98textNO
E39Cell Value<59.5textNO
E39Cell Value>60.5textNO
D39Cell Value<39.5textNO
D39Cell Value>40.5textNO
E40Cell Value>102textNO
E39Cell Valuebetween 59.5 and 60.5textNO
E37Cell Value<34.7textNO
D38:E38Cell Value>15.3textNO
D38:E38Cell Value<14.7textNO
F35:F36Cell Value<20.7textNO
F35:F36Cell Value>21.1textNO
D37Cell Value<49.7textNO
D37Cell Value>50.3textNO
D38:E38Cell Valuebetween 14.7 and 15.3textNO
F35:F36Cell Valuebetween 20.7 and 21.1textNO
D35:E36Cell Value<-0.02textNO
F37Cell Valuebetween 0 and 0.1textNO
E37Cell Valuebetween 34.7 and 35.3textNO
D37Cell Valuebetween 49.7 and 50.3textNO
J24:J25Cell Value<-101textNO
D39Cell Valuebetween 39.5 and 40.5textNO
N31Cell Value>2textNO
J24:J25Cell Value>-99textNO
Cells with Data Validation
CellAllowCriteria
K32Whole numberbetween (M32-K32)-2 and (M32-K32)+2
L32Any value
D24:J26Any value
B28:B30Whole numberbetween 1 and 2
B17Whole numberbetween 101 and 102
B9:B14Whole numberbetween 101 and 102
I9List='Drop Down lists'!$D$1:$D$8
B5:C5List='Drop Down lists'!$A$1:$A$7
 
Glad you sorted it & thanks for the feedback. Although that still clears the undo stack for me.
However you can simplify it like
VBA Code:
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
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Glad you sorted it & thanks for the feedback. Although that still clears the undo stack for me.
However you can simplify it like
VBA Code:
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
 
Upvote 0
It should work as long as V3 is being changed manually, although you originally had it as a formula.
 
Upvote 0
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.
 
Upvote 0
Yes but that would take you back to what you had before.
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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
Back
Top