Remove >70% efficiency from employee report

[DPARDY]

Thank you in advance for your time and help.

I have a spreadsheet that I export daily from our system and I have got it to this point. My next step that I am tiring to do is, everyone that has a greater than 70% efficiency (column S), I want to remove/hide all their info from the file. The problem I am having is that the number of jobs varies day to day per person with 50+ employees.

Thanks again.

 

[kevin9999]

Could you provide a copy of your sheet using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform?

 

[kevin9999]

Please try the following on a copy of your worksheet. Run the macro with the sheet active.

Option Explicit
Sub Test()
    Dim i As Long
    For i = Cells(Rows.Count, 19).End(xlUp).Row To 8 Step -1
        If Cells(i, 19) > 0.7 Then
            Rows(i).Hidden = True
            i = i - 1
            Do While WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, 12))) <> 0 And Cells(i, 2) <> "Job"
                Rows(i).Hidden = True
                i = i - 1
            Loop
        End If
    Next i
End Sub

 

[DPARDY]

Please try the following on a copy of your worksheet. Run the macro with the sheet active.

Option Explicit
Sub Test()
    Dim i As Long
    For i = Cells(Rows.Count, 19).End(xlUp).Row To 8 Step -1
        If Cells(i, 19) > 0.7 Then
            Rows(i).Hidden = True
            i = i - 1
            Do While WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, 12))) <> 0 And Cells(i, 2) <> "Job"
                Rows(i).Hidden = True
                i = i - 1
            Loop
        End If
    Next i
End Sub

kevin9999,

This is what happened, it hid just about everything.

 

[kevin9999]

OK, then back to post #2. I'll really need your actual sheet before I can look at this further.

 

[DPARDY]

OK, then back to post #2. I'll really need your actual sheet before I can look at this further.

No problem, how do i do that, i don't see anything to attach a file.

 

[DPARDY]

LR_EmployeeEfficiency TEST.xlsx

Sheet1 20-Dec-23 02:52PM,Employee Efficiency From 12/19/2023 Thru 12/19/2023 Setup,Run Job,WC Oper,Est Hrs,Adj E Hrs,Act Hrs,% Eff,Qty,Est Hrs,Adj E Hrs,Act Hrs,% Eff Employee:,133,SMITH, JANE 136590,VARIAXIS-1,MACHINED VALVE BODY 3" Y-PATT,10,0,/,ea,5.60,0% 136737,PTH-6800-1,COUPLING MACHININ...

docs.google.com

Here is a link, thank you.

 

[kevin9999]

Thank you for that. The issue was the custom formatting you have on the % figures. The code below had been adjusted for that:

Option Explicit
Sub Test_2()
    Dim i As Long
    For i = Cells(Rows.Count, 19).End(xlUp).Row To 8 Step -1
        If Cells(i, 19) > 70 Then
            Rows(i).Hidden = True
            i = i - 1
            Do While WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, 12))) <> 0 And Cells(i, 2) <> "Job"
                Rows(i).Hidden = True
                i = i - 1
            Loop
        End If
    Next i
End Sub

Leading to, before:

LR_EmployeeEfficiency TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		20-Dec-23 02:52PM						Employee Efficiency
3
4								From 12/19/2023 Thru 12/19/2023
5
6
7									Setup							Run
8		Job		WC Oper				Est Hrs	Adj E Hrs	Act Hrs	% Eff	Qty			Est Hrs	Adj E Hrs	Act Hrs			% Eff
9		Employee:	133	SMITH, JANE
10		136590		VARIAXIS-1	MACHINED VALVE BODY 3" Y-PATT		10					0	/	ea			5.60			0%
11		136737		PTH-6800-1	COUPLING MACHINING		10					16	/	ea	19.00	2.53	2.40			106%
12		202312		OH			50	0.00	0.00	2.00	0%	0	/	ea
13						Employee Total:			0.00	2.00	0%					2.53	8.00			32%
14																		Total Eff:	25.3%
15
16		Employee:	134	BOND, JAMES
17		136231		PTH-6800-2	CAM SUPORT, VE, FR, LT		10					1	/	ea	165.00	2.75	4.00			69%
18		136315		PTH-6800-2	YOKE MOUNT		20					2	/	ea	73.50	1.40	1.00			140%
19		136720		PTH-6800-1	HINGE, DOOR SD, CHRG, L/R		20	4.00	2.40	3.00	80%	6	/	ea	13.80	2.30	3.00			77%
20		202312		DOWNTIME			30	0.00	0.00	3.00	0%	0	/	ea
21						Employee Total:			2.40	6.00	80%					6.45	8.00			81%
22																		Total Eff:	80.5%
23
24		Employee:	137	WICK, JOHN
25		136625		TC-MY250	HUB		70	2.00	1.00	1.50	67%	1	/	ea	2.00	1.57	5.50			29%
26		202312		DOWNTIME			30	0.00	0.00	3.00	0%	0	/	ea
27						Employee Total:			1.00	4.50	22%					1.57	5.50			29%
28																		Total Eff:	25.7%
29
30
31						Report Total:			24.70	41.49	59.5%					191.38	225.84			84.7%
32
33																Total Shop Hours:				267.33
34																Shop Efficiency:				80.8%
Sheet1 (2)

After:

LR_EmployeeEfficiency TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		20-Dec-23 02:52PM						Employee Efficiency
3
4								From 12/19/2023 Thru 12/19/2023
5
6
7									Setup							Run
8		Job		WC Oper				Est Hrs	Adj E Hrs	Act Hrs	% Eff	Qty			Est Hrs	Adj E Hrs	Act Hrs			% Eff
9		Employee:	133	SMITH, JANE
10		136590		VARIAXIS-1	MACHINED VALVE BODY 3" Y-PATT		10					0	/	ea			5.60			0%
11		136737		PTH-6800-1	COUPLING MACHINING		10					16	/	ea	19.00	2.53	2.40			106%
12		202312		OH			50	0.00	0.00	2.00	0%	0	/	ea
13						Employee Total:			0.00	2.00	0%					2.53	8.00			32%
14																		Total Eff:	25.3%
15
23
24		Employee:	137	WICK, JOHN
25		136625		TC-MY250	HUB		70	2.00	1.00	1.50	67%	1	/	ea	2.00	1.57	5.50			29%
26		202312		DOWNTIME			30	0.00	0.00	3.00	0%	0	/	ea
27						Employee Total:			1.00	4.50	22%					1.57	5.50			29%
28																		Total Eff:	25.7%
29
30
31						Report Total:			24.70	41.49	59.5%					191.38	225.84			84.7%
32
33																Total Shop Hours:				267.33
34																Shop Efficiency:				80.8%
Sheet1 (2)

 

[DPARDY]

Thank you for that. The issue was the custom formatting you have on the % figures. The code below had been adjusted for that:

Option Explicit
Sub Test_2()
    Dim i As Long
    For i = Cells(Rows.Count, 19).End(xlUp).Row To 8 Step -1
        If Cells(i, 19) > 70 Then
            Rows(i).Hidden = True
            i = i - 1
            Do While WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, 12))) <> 0 And Cells(i, 2) <> "Job"
                Rows(i).Hidden = True
                i = i - 1
            Loop
        End If
    Next i
End Sub

Leading to, before:

LR_EmployeeEfficiency TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		20-Dec-23 02:52PM						Employee Efficiency
3
4								From 12/19/2023 Thru 12/19/2023
5
6
7									Setup							Run
8		Job		WC Oper				Est Hrs	Adj E Hrs	Act Hrs	% Eff	Qty			Est Hrs	Adj E Hrs	Act Hrs			% Eff
9		Employee:	133	SMITH, JANE
10		136590		VARIAXIS-1	MACHINED VALVE BODY 3" Y-PATT		10					0	/	ea			5.60			0%
11		136737		PTH-6800-1	COUPLING MACHINING		10					16	/	ea	19.00	2.53	2.40			106%
12		202312		OH			50	0.00	0.00	2.00	0%	0	/	ea
13						Employee Total:			0.00	2.00	0%					2.53	8.00			32%
14																		Total Eff:	25.3%
15
16		Employee:	134	BOND, JAMES
17		136231		PTH-6800-2	CAM SUPORT, VE, FR, LT		10					1	/	ea	165.00	2.75	4.00			69%
18		136315		PTH-6800-2	YOKE MOUNT		20					2	/	ea	73.50	1.40	1.00			140%
19		136720		PTH-6800-1	HINGE, DOOR SD, CHRG, L/R		20	4.00	2.40	3.00	80%	6	/	ea	13.80	2.30	3.00			77%
20		202312		DOWNTIME			30	0.00	0.00	3.00	0%	0	/	ea
21						Employee Total:			2.40	6.00	80%					6.45	8.00			81%
22																		Total Eff:	80.5%
23
24		Employee:	137	WICK, JOHN
25		136625		TC-MY250	HUB		70	2.00	1.00	1.50	67%	1	/	ea	2.00	1.57	5.50			29%
26		202312		DOWNTIME			30	0.00	0.00	3.00	0%	0	/	ea
27						Employee Total:			1.00	4.50	22%					1.57	5.50			29%
28																		Total Eff:	25.7%
29
30
31						Report Total:			24.70	41.49	59.5%					191.38	225.84			84.7%
32
33																Total Shop Hours:				267.33
34																Shop Efficiency:				80.8%
Sheet1 (2)

After:

LR_EmployeeEfficiency TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		20-Dec-23 02:52PM						Employee Efficiency
3
4								From 12/19/2023 Thru 12/19/2023
5
6
7									Setup							Run
8		Job		WC Oper				Est Hrs	Adj E Hrs	Act Hrs	% Eff	Qty			Est Hrs	Adj E Hrs	Act Hrs			% Eff
9		Employee:	133	SMITH, JANE
10		136590		VARIAXIS-1	MACHINED VALVE BODY 3" Y-PATT		10					0	/	ea			5.60			0%
11		136737		PTH-6800-1	COUPLING MACHINING		10					16	/	ea	19.00	2.53	2.40			106%
12		202312		OH			50	0.00	0.00	2.00	0%	0	/	ea
13						Employee Total:			0.00	2.00	0%					2.53	8.00			32%
14																		Total Eff:	25.3%
15
23
24		Employee:	137	WICK, JOHN
25		136625		TC-MY250	HUB		70	2.00	1.00	1.50	67%	1	/	ea	2.00	1.57	5.50			29%
26		202312		DOWNTIME			30	0.00	0.00	3.00	0%	0	/	ea
27						Employee Total:			1.00	4.50	22%					1.57	5.50			29%
28																		Total Eff:	25.7%
29
30
31						Report Total:			24.70	41.49	59.5%					191.38	225.84			84.7%
32
33																Total Shop Hours:				267.33
34																Shop Efficiency:				80.8%
Sheet1 (2)

That worked like a charm, but I tired it on a full list and what I am getting is when there is a group of >70 together I am ending up with a group of blank rows together. What would I need to change to have it include one more row up or down to be hidden as well? Example file row 15 or 23.

Thanks Kevin.

 

[kevin9999]

Understood
Please try the following:

Option Explicit
Sub Test_3()
    Dim i As Long
    For i = Cells(Rows.Count, 19).End(xlUp).Row To 8 Step -1
        If Cells(i, 19) > 70 Then
            Rows(i).Hidden = True
            i = i - 1
            Do While WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, 12))) <> 0 And Cells(i, 2) <> "Job"
                Rows(i).Hidden = True
                If WorksheetFunction.CountA(Rows(i - 1)) = 0 Then Rows(i - 1).Hidden = True
                i = i - 1
            Loop
        End If
    Next i
End Sub