variable vba per ws

[JOEE1979]

I need to write a vba that will be variable to every worksheet in my book.
I want to delete the blank cells (shift cells up) in starting with AE4-AI4, and all the way down until the vba starts to see text (in this example, AE37)
AE37 is going to be different in every ws, (the columns will always remain the same, however the rows will be different)
Is this possible?

Weekday Bus Cycling - Revision 3 - done.xlsm
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
3	Block	Type	Op Day	Sign on	Start	End	Veh	Km	Note	Total KM	Time			OUT	IN
4							DDS			4924.41
5										644.82
6										659.54
7										764.61
8										405.92
9										599.54
10										653.42
11										403.60
12										425.73
13										367.24
14
15
16
17				0		0
18
19
20	Block	Type	Op Day	Sign on	Start	End	Veh	Km	Note	Total KM
21							45AC			2334.37
22										463.43
23										531.50
24										599.93
25										739.51
26
27
28	C	Change off or wash bus, if required
29	M	Carry the Mail
30	RPT	Contact supervisor for instructions
31
32
33				0		0
34
35
36
37											0:01	-	0:30	0	0
38											0:31	-	1:00	0	0
39											1:01	-	1:30	0	0
40											1:31	-	2:00	0	0
41											2:01	-	2:30	0	0
42											2:31	-	3:00	0	0
43											3:01	-	3:30	1	0
44											3:31	-	4:00	2	0
45											4:01	-	4:30	1	0
46											4:31	-	5:00	1	0
47											5:01	-	5:30	0	0
48											5:31	-	6:00	2	0
49											6:01	-	6:30	1	0
50											6:31	-	7:00	2	0
51											7:01	-	7:30	1	0
52											7:31	-	8:00	0	0
53											8:01	-	8:30	0	0
54											8:31	-	9:00	0	0
55											9:01	-	9:30	0	0
56											9:31	-	10:00	0	0
57											10:01	-	10:30	0	0
58											10:31	-	11:00	0	0
59											11:01	-	11:30	0	1
60											11:31	-	12:00	0	1
61											12:01	-	12:30	1	2
62											12:31	-	13:00	0	0
63											13:01	-	13:30	1	0
64											13:31	-	14:00	0	0
65											14:01	-	14:30	2	0
66											14:31	-	15:00	2	0
67											15:01	-	15:30	2	1
68											15:31	-	16:00	0	1
69											16:01	-	16:30	2	0
70											16:31	-	17:00	0	0
71											17:01	-	17:30	0	0
72											17:31	-	18:00	0	0
73											18:01	-	18:30	0	0
74											18:31	-	19:00	0	0
75											19:01	-	19:30	0	1
76											19:31	-	20:00	0	0
77											20:01	-	20:30	0	0
78											20:31	-	21:00	0	0
79											21:01	-	21:30	0	1
80											21:31	-	22:00	0	0
81											22:01	-	22:30	0	0
82											22:31	-	23:00	0	1
83											23:01	-	23:30	0	1
84											23:31	-	0:00	0	2
Aberfoyle-MON-WED

Cell Formulas
Range	Formula
AD4	AD4	=SUM(AD5:AD16)
AD5:AD16,AD22:AD32	AD5	=IF((SUM(H5,R5,AB5))=0,"",SUM(H5,R5,AB5))
X17,Z17	X17	=COUNTIF(X5:X16,">="&Planner!$C$1)
AD21	AD21	=SUM(AD22:AD32)
X33,Z33	X33	=COUNTIF(X22:X32,">="&Planner!$C$1)
AH37:AH84	AH37	=SUM((COUNTIFS($D$5:$D$16,">="&AE37,$D$5:$D$16,"<="&AG37))+(COUNTIFS($D$22:$D$32,">="&AE37,$D$22:$D$32,"<="&AG37))+(COUNTIFS($N$5:$N$16,">="&AE37,$N$5:$N$16,"<="&AG37))+(COUNTIFS($N$22:$N$32,">="&AE37,$N$22:$N$32,"<="&AG37))+(COUNTIFS($X$5:$X$16,">="&AE37,$X$5:$X$16,"<="&AG37))+(COUNTIFS($X$22:$X$32,">="&AE37,$X$22:$X$32,"<="&AG37)))
AI37:AI84	AI37	=SUM((COUNTIFS($F$5:$F$16,">="&AE37,$F$5:$F$16,"<="&AG37))+(COUNTIFS($F$22:$F$32,">="&AE37,$F$22:$F$32,"<="&AG37))+(COUNTIFS($P$5:$P$16,">="&AE37,$P$5:$P$16,"<="&AG37))+(COUNTIFS($P$22:$P$32,">="&AE37,$P$22:$P$32,"<="&AG37))+(COUNTIFS($Z$5:$Z$16,">="&AE37,$Z$5:$Z$16,"<="&AG37))+(COUNTIFS($Z$22:$Z$32,">="&AE37,$Z$22:$Z$32,"<="&AG37)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI37:AI84	Cell Value	>0	text	NO
AH37:AH84	Cell Value	>0	text	NO
AH37:AI84	Cell Value	=0	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

 

[kevin9999]

Try this on a copy of your workbook.

Sub JOEE1979()
    Dim ws As Worksheet, rng As Range, LRow As Long
    
    For Each ws In ThisWorkbook.Worksheets
        LRow = ws.Cells(Rows.Count, "AE").End(xlUp).Row
        Set rng = ws.Range("AE4:AI" & LRow).SpecialCells(xlCellTypeBlanks)
        rng.Delete shift:=xlUp
    Next ws
End Sub

 

[JOEE1979]

Try this on a copy of your workbook.

Sub JOEE1979()
    Dim ws As Worksheet, rng As Range, LRow As Long
   
    For Each ws In ThisWorkbook.Worksheets
        LRow = ws.Cells(Rows.Count, "AE").End(xlUp).Row
        Set rng = ws.Range("AE4:AI" & LRow).SpecialCells(xlCellTypeBlanks)
        rng.Delete shift:=xlUp
    Next ws
End Sub

"no cells found"
and then highlights "Set rng = ws.Range("AE4:AI" & LRow).SpecialCells(xlCellTypeBlanks)"

 

[kevin9999]

Sorry Joe, I assumed all your sheets had blank cells in that range. I'm away from my normal laptop for a few hours so I can't test this, but give this variation a try.

Sub JOEE1979_V2()
    Dim ws As Worksheet, rng As Range, LRow As Long
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("AE4") = "" Then
            LRow = ws.Cells(Rows.Count, "AE").End(xlUp).Row
            Set rng = ws.Range("AE4:AI" & LRow).SpecialCells(xlCellTypeBlanks)
            rng.Delete shift:=xlUp
        End If
    Next ws
End Sub

 

[JOEE1979]

Sorry Joe, I assumed all your sheets had blank cells in that range. I'm away from my normal laptop for a few hours so I can't test this, but give this variation a try.

Sub JOEE1979_V2()
    Dim ws As Worksheet, rng As Range, LRow As Long
   
    For Each ws In ThisWorkbook.Worksheets
        If ws.Range("AE4") = "" Then
            LRow = ws.Cells(Rows.Count, "AE").End(xlUp).Row
            Set rng = ws.Range("AE4:AI" & LRow).SpecialCells(xlCellTypeBlanks)
            rng.Delete shift:=xlUp
        End If
    Next ws
End Sub

worked, awesome, thanks

 

[kevin9999]

worked, awesome, thanks

Happy to help Joe, and thanks for the feedback