delete row based on 3 criteria

[JOEE1979]

This is way out of my league, and I need help please.

Here is what I'm looking to do;

If there is a number in "T5" and nothing in "A5" & "K5" & "U5" (all 3 have to be blank),
Then delete entire row
(I would like this to continue until end, every sheet has a different amount of lines)

***I would like this to be a 1 time event and not re-occur)***


Thank you

Weekday Bus Cycling - Revision2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Current Time		22:28			Ready Units in Barn				1		Good Until		Tomorrow			Next Departure			Tomorrow						Aberfoyle-MON-WED									0
2	Required By					Required Units			0	Units Returning			0	Total Required			0	Total Returning			13
3	Block	Type	Op Day	Sign on	Start	End	Veh	Km	Note		Block	Type	Op Day	Sign on	Start	End	Veh	Km	Note		Block	Type	Op Day	Sign on	Start	End	Veh	Km	Note	Total KM	Time			OUT	IN
4	Super Low Top - Double Decker						DDS										DDS										DDS			5877.46	0:01	-	0:30	0	0
5	MDDS2101	REG	muwtf	3:47	4:05	11:47	DDS	251.74	RPT	1	MDDS2104	REL	muwtf	14:17	14:35	0:26	DDS	393.09		1	aberf005	REL	muwtf	13:24	13:42	21:20	DDS	224.54		869.36	0:31	-	1:00	0	0
6	DDS21001	REG	muwtf	4:32	4:50	15:05	DDS	383.98		2	MDDS2106	REL	muwtf	15:21	15:39	23:15	DDS	275.56		2	aberf006	REL	muwtf	14:02	14:20	23:55	DDS	342.85		1002.39
7	DDS21002	REG	muwtf	5:32	5:50	15:40	DDS	383.98		3	MDDS2108	REL	muwtf	16:02	16:20	3:01	DDS	380.63		3	aberf007	REL	muwtf	14:47	15:05	0:35	DDS	385.66		1150.27
8	DDS21003	REG/SPLT	muwtf	6:47	7:05	19:05	DDS	405.92		4										4										405.92
9	MDDS2103	REG/REL/REG/REL	muw	6:47	7:05	22:55	DDS	599.54		5										5										599.54
10	MDDS2102	REG/REL	muwtf	5:57	6:15	1:00	DDS	653.42		6										6										653.42
11										7	MDDS2105	REL	muwtf	14:32	14:50	23:35	DDS	403.60		7										403.60
12										8	MDDS2107	REL	muwtf	15:24	15:42	2:35	DDS	425.73		8										425.73
13										9	MDDS2109	REL	muwtf	16:07	16:25	2:16	DDS	367.24		9										367.24
14
15
16
17				0		2								0		6								0		2
Aberfoyle-MON-WED

Cell Formulas
Range	Formula
C1	C1	=Planner!$C$1
N1	N1	=HLOOKUP($Z$1,Planner!$C$3:$AL$7,5,FALSE)
T1	T1	=HLOOKUP($Z$1,Planner!$C$3:$AL$7,2,FALSE)
AI1	AI1	=SUM((COUNTIFS($D$5:$D$16,">="&Planner!$C$1,$D$5:$D$16,"<="&$N$1))+(COUNTIFS($N$5:$N$16,">="&Planner!$C$1,$N$5:$N$16,"<="&$N$1))+(COUNTIFS($D$22:$D$32,">="&Planner!$C$1,$D$22:$D$32,"<="&$N$1))+(COUNTIFS($N$22:$N$32,">="&Planner!$C$1,$N$22:$N$32,"<="&$N$1))+(COUNTIFS($X$5:$X$16,">="&Planner!$C$1,$X$5:$X$16,"<="&$N$1))+(COUNTIFS($X$22:$X$32,">="&Planner!$C$1,$X$22:$X$32,"<="&$N$1)))
I2	I2	=SUM((COUNTIFS($D$5:$D$16,">="&Planner!$C$1,$D$5:$D$16,"<="&$C$2))+(COUNTIFS($D$22:$D$32,">="&Planner!$C$1,$D$22:$D$32,"<="&$C$2))+(COUNTIFS($N$5:$N$16,">="&Planner!$C$1,$N$5:$N$16,"<="&$C$2))+(COUNTIFS($N$22:$N$32,">="&Planner!$C$1,$N$22:$N$32,"<="&$C$2))+(COUNTIFS($X$5:$X$16,">="&Planner!$C$1,$X$5:$X$16,"<="&$C$2))+(COUNTIFS($X$22:$X$32,">="&Planner!$C$1,$X$22:$X$32,"<="&$C$2)))
M2	M2	=SUM((COUNTIFS($F$5:$F$16,">="&Planner!$C$1,$F$5:$F$16,"<="&$C$2))+(COUNTIFS($F$22:$F$32,">="&Planner!$C$1,$F$22:$F$32,"<="&$C$2))+(COUNTIFS($P$5:$P$16,">="&Planner!$C$1,$P$5:$P$16,"<="&$C$2))+(COUNTIFS($P$22:$P$32,">="&Planner!$C$1,$P$22:$P$32,"<="&$C$2))+(COUNTIFS($Z$5:$Z$16,">="&Planner!$C$1,$Z$5:$Z$16,"<="&$C$2))+(COUNTIFS($Z$22:$Z$32,">="&Planner!$C$1,$Z$22:$Z$32,"<="&$C$2)))
Q2	Q2	=SUM($D$17,$N$17,$D$33,$N$33,$X$17,$X$33)
U2	U2	=SUM($F$17,$P$17,$F$33,$P$33,$Z$17,$Z$33)
Z1	Z1	=MID(CELL("filename",$V$1),FIND("]",CELL("filename",$V$1))+1,32)
Z2	Z2	=IF($AI$1>$J$1,"Will require "&(SUM($AI$1-$J$1))&" more units for "&TEXT($N$1,"hh:mm")&" service","")
AH4:AH5	AH4	=SUM((COUNTIFS($D$5:$D$13,">="&AE4,$D$5:$D$13,"<="&AG4))+(COUNTIFS($D$16:$D$23,">="&AE4,$D$16:$D$23,"<="&AG4))+(COUNTIFS($N$5:$N$26,">="&AE4,$N$5:$N$26,"<="&AG4))+(COUNTIFS($N$29:$N$43,">="&AE4,$N$29:$N$43,"<="&AG4))+(COUNTIFS($X$5:$X$21,">="&AE4,$X$5:$X$21,"<="&AG4))+(COUNTIFS($X$24:$X$48,">="&AE4,$X$24:$X$48,"<="&AG4)))
AI4:AI5	AI4	=SUM((COUNTIFS($F$5:$F$13,">="&AE4,$F$5:$F$13,"<="&AG4))+(COUNTIFS($F$16:$F$21,">="&AE4,$F$16:$F$21,"<="&AG4))+(COUNTIFS($P$5:$P$22,">="&AE4,$P$5:$P$22,"<="&AG4))+(COUNTIFS($P$25:$P$37,">="&AE4,$P$25:$P$37,"<="&AG4))+(COUNTIFS($Z$5:$Z$19,">="&AE4,$Z$5:$Z$19,"<="&AG4))+(COUNTIFS($Z$22:$Z$29,">="&AE4,$Z$22:$Z$29,"<="&AG4)))
AD4	AD4	=SUM(AD5:AD16)
AD5:AD16	AD5	=IF((SUM(H5,R5,AB5))=0,"",SUM(H5,R5,AB5))
D17,Z17,X17,P17,N17,F17	D17	=COUNTIF(D5:D16,">="&Planner!$C$1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI4:AI51	Cell Value	>0	text	NO
AH4:AH51	Cell Value	>0	text	NO
AH4:AI51	Cell Value	=0	text	NO
N1:O1	Cell Value	contains "Tomorrow"	text	NO
D5:D16,N5:N16,X5:X16,D22:D32,N22:N32,X22:X27,X31:X32	Expression	=D5=$N$1	text	NO
D5:D16,N5:N16,X5:X16,D22:D32,N22:N32,X22:X27,X31:X32	Cell Value	between Planner!$C$1 and $N$1	text	NO
D5:D16,N5:N16,X5:X16,D22:D32,N22:N32,X22:X27,X31:X32	Cell Value	>Planner!$C$1	text	NO
F5:F16,P5:P16,Z5:Z16,F22:F32,P22:P32,Z22:Z27,Z31:Z32	Cell Value	>Planner!$C$1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J1	Whole number	>0

 

[Michael M]

Dont have excel atm, so UNTESTED

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "T").End(xlUp).Row
For r = lr To 5 Step -1
    If IsNumber(Range("T" & r)) And Range("A" & r) = "" And Range("K" & r) = "" And Range("U" & r) = "" Then
        Rows(r).Delete
    End If
Next r
End Sub

 

[JOEE1979]

I get a message
Sub or function not defined

IsNumber (is highlighted)

***Also, how would I make this for entire workbook?***

 

[Michael M]

still untesteed

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
For Each ws In Worksheets
lr = ws.Cells(Rows.Count, "T").End(xlUp).Row
With ws
For r = lr To 5 Step -1
    If .Range("T" & r) <> "" And .Range("A" & r) = "" And .Range("K" & r) = "" And .Range("U" & r) = "" Then
        .Rows(r).Delete
    End If
Next r
End With
Next ws
End Sub

 

[JOEE1979]

still untesteed

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
For Each ws In Worksheets
lr = ws.Cells(Rows.Count, "T").End(xlUp).Row
With ws
For r = lr To 5 Step -1
    If .Range("T" & r) <> "" And .Range("A" & r) = "" And .Range("K" & r) = "" And .Range("U" & r) = "" Then
        .Rows(r).Delete
    End If
Next r
End With
Next ws
End Sub

worked, awesome, thanks

 

[Michael M]

Glad to help...