Help on my filter please

[Eric Penfold]

Please could you help as to why the filter will not work in this code. Need yesterdays & todays filtered.

Sub FindValues()

    Dim ws           As Worksheet
    Dim Rng          As Range
    Dim Lrow         As Long, Yesterday As Long, ToDay As Long
    Dim Comparerng   As Variant, x As Variant, y As Variant, i As Variant
    Dim Sourcerng    As Range
    Dim YDate        As Date
    Dim TDate        As Date
    
    On Error Resume Next
    Set ws = ActiveSheet
    Lrow = ws.Range("A2").End(xlDown).Row
    Set Rng = ws.Range("A2:J" & Lrow)
    
        ToDay = (DateSerial(Year(Date), Month(Date), Day(Date)))
        TDate = ToDay
        TDate = Format(TDate, "mm/dd/yyyy")
        
        Yesterday = (DateSerial(Year(Date), Month(Date), Day(Date - 1)))
        YDate = Yesterday
        YDate = Format(YDate, "mm/dd/yyyy")
        
        Rng.AutoFilter Field:=1, _
        Criteria1:=">" & YDate, _
        Operator:=xlAnd, _
        Criteria2:="<" & TDate

       End Sub

 

[Eric Penfold]

Thanks Eric. Try this now: (EDITED)

Sub Filter_2_days_v2()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "dd/mm/yyyy"), 2, Format(Date - 1, "dd/mm/yyyy"), 0
    End With
End Sub

Thanks Kevin but it just shows today not today & yesterday??

 

[kevin9999]

Thanks Kevin but it just shows today not today & yesterday??

When I test it on a small dummy data set, it shows both.

Before

filter.xlsm
	A	B	C	D	E	F	G	H	I	J
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10
2	10/06/2022	data	data	data	data	data	data	data	data	data
3	17/06/2022	data	data	data	data	data	data	data	data	data
4	11/06/2022	data	data	data	data	data	data	data	data	data
5	01/06/2022	data	data	data	data	data	data	data	data	data
6	11/06/2022	data	data	data	data	data	data	data	data	data
7	10/06/2022	data	data	data	data	data	data	data	data	data
8	16/06/2022	data	data	data	data	data	data	data	data	data
9	16/06/2022	data	data	data	data	data	data	data	data	data
10	15/06/2022	data	data	data	data	data	data	data	data	data
11	15/06/2022	data	data	data	data	data	data	data	data	data
12	17/06/2022	data	data	data	data	data	data	data	data	data
13
Sheet1

After

filter.xlsm
	A	B	C	D	E	F	G	H	I	J
1	header1	header2	header3	header4	header5	header6	header7	header8	header9	header10
3	17/06/2022	data	data	data	data	data	data	data	data	data
8	16/06/2022	data	data	data	data	data	data	data	data	data
9	16/06/2022	data	data	data	data	data	data	data	data	data
12	17/06/2022	data	data	data	data	data	data	data	data	data
13
Sheet1

Is there any chance you could provide a sample using the XL2BB Tool?

 

[Eric Penfold]

2022 Coventry Back Order.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Order Date	Customer Name	Order No.	Ent By	Code	Description	BO Qty.	Intact Stock Levels	Notes	Back Order Reason Code	Supplier Name	Depot	Back Order Release Date	Days to Fulfil
2	01/06/2022	T Vaughan Ltd	SO0061658	NIJ	VFFSC175	150-175mm Flexible Coupling VSC175	10	5		LS	VIP001	C		-31938
3	01/06/2022	Hannon Brothers Ltd	SO0061620	NIJ	SF27/100	80g Non-Woven GP Geotextile 4.5 x 100m Roll	1	25		ONI	DON001	C		-31938
4	01/06/2022	Oliver Curran Construction Ltd	SO0061597	NIJ	4VGRO	Bottle Gully Raising Piece x225mm High Orange	6	0		LS	PLA002	C		-31938
5	01/06/2022	JD Excavations Tyrone Ltd	SO0061618	NIJ	450VCRA	450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown	24	0		ONI	BRE002	C		-31938
6	01/06/2022	M&T Midlands Ltd	SO0061365	NIJ	375TWCN	375mm Twinwall Pipe Coupler	1	0		ONI	NAY001	C		-31938
7	01/06/2022	Elmcroft Construction Ltd	SO0061655	NIJ	225R150	225/150mm ID (172mm) D/Socket Naylor TW Drain Reducer	1	13		LS	NAY001	C		-31938
8	01/06/2022	LF Solutions Ltd	SO0061265	NIJ	10VF90	250mm S/Socket Bend 87.5deg	2	0		LS	RED002	C		-31938
9	01/06/2022	LF Solutions Ltd	SO0061265	NIJ	10VF45	250mm S/Socket Bend 45deg	1	0		LS	RED002	C		-31938
10	01/06/2022	LF Solutions Ltd	SO0061265	NIJ	10VF15	250mm S/Socket Bend 15deg	5	0		LS	RED002	C		-31938
11	01/06/2022	Hannon Brothers Ltd	SO0061616	NIJ	POLYT	Please note above duct is standard twinwall electric duct. Not compliant with ENATS 12-24 or EDF/SSE requirements.	1	0		ONI	NONE	C		-31938
12	01/06/2022	Hannon Brothers Ltd	SO0061616	NIJ	110RTWBL	110mm OD Twinwall Electrical Duct x6m; Black c/w Coupler	15	18		NSI	NAY001	C		-31938
13	01/06/2022	West Point Construction Limited	SO0061485	NIJ	100PGD50	Perforated Gas Duct: 100mm x 50m Coil; Yellow	5	0		LS	NAY001	C		-31938
14	01/06/2022	Southern Mastic Roofing Ltd	SO0061437	NIJ	33.0304	Frost Inverted Roof drain assembly - fixed dome 220mm and 150mm gravel guard, medium sump body with clamp, vertical threaded outlet 4" BSP	11	0		NSI	NONE	C		-31938
15	02/06/2022	Gtek Plant Ltd	SO0061251	NG	450TDBLN	450mm BBA Twinwall Plain Ended Pipe x6m	6	0		ONI	NAY001	C		-31939
16	02/06/2022	Carey London Ltd	SO0058403	LK	31.6517	Frost Spigot Adaptor BSP 3 to 82mm plastic	17	0		ONI	JIM001	C		-31939
17	02/06/2022	O'Halloran & O'Brien Ltd	SO0052299	SM	31.6002	Frost Extension cast iron 140mm NPSM4	27	0		ONI	HEB001	C		-31939
18	02/06/2022	O'Halloran & O'Brien Ltd	SO0052299	SM	31.1071	Frost Body S trap cast iron vertical outlet 100mm DIA supplied with 3 x 2" BSP plugged inlets	1	0		LS	HEB001	C		-31939
19	02/06/2022	Slane Construction Ltd	SO0052069	LK	31.0275.S	Frost Floor drain 178mm circular stainless steel grating for vinyl floors with medium sump -'S' trap - spigot outlet size 100mm	1	0		LS	FRO001	C		-31939
20	06/06/2022	Norcon Construction Ltd	SO0061805	NIJ	63TWOS	63mm OD Twinwall Duct x50m; Street Lighting Orange	2	0		ONI				-31941
21	06/06/2022	Flatbase Construction Services Ltd	SO0061545	NIJ	63PTNE90	63mm Puriton Pipe Elbow 90deg; Type A	3	0		LS				-31941
22	06/06/2022	Flatbase Construction Services Ltd	SO0061545	NIJ	63PTN50	Puriton Barrier Pipe: 63mm x 50m Coil; Type A SDR11	2	0		LS				-31941
23	06/06/2022	RM Contractors Ltd	SO0061733	NIJ	32PD100	32/37mm Polyethylene Duct x100m Coil; Black Printed Electric	1	0		ONI				-31941
24	06/06/2022	A D Bly Construction Ltd	SO0060761	NG	225R160	225mm Naylor TW Socket to 160mm UPVC Socket Level Invert Reducer	3	-1		ONI				-31941
25	06/06/2022	J Holland Construction Ltd	SO0061635	NIJ	110TWBL	110mm OD Twinwall Electrical Duct x50m; Black	1	0		ONI				-31941
26	06/06/2022	Norcon Construction Ltd	SO0061726	NIJ	100PGD50	Perforated Gas Duct: 100mm x 50m Coil; Yellow	3	0		ONI				-31941
27	07/06/2022	Goldmax Ltd	SO0061716	LK	PB160	158-170mm Zinc Plated Pipe Bracket Rubber Lining - M8/M10 Thread	100	0		LS				-31942
28	07/06/2022	Agetur UK Ltd	SO0061735	NIJ	63TWBL	63mm OD Twinwall Duct x50m; Black	4	0		ONI				-31942
29	07/06/2022	ALBY Associates Ltd	SO0061729	LK	160TWBL25	160mm OD Twinwall Electrical Duct x25m Coil Black (140mm ID)	4	0		ONI				-31942
30	07/06/2022	M&T Midlands Ltd	SO0061845	NIJ	300RTWBLN	300mm ID Twinwall Duct x6m; Black; P/E	1	0		NSI				-31942
31	07/06/2022	M&T Midlands Ltd	SO0061845	NIJ	110TWBL	110mm OD Twinwall Electrical Duct x50m; Black	4	0		ONI				-31942
32	07/06/2022	McCoy Contractors Ltd	SO0061917	NIJ	110TW90	110mm OD Twinwall Duct Bend Long Radius 90deg; P/E	9	0		ONI				-31942
33	07/06/2022	J Holland Construction Ltd	SO0061911	NIJ	110TW90	110mm OD Twinwall Duct Bend Long Radius 90deg; P/E	2	0		ONI				-31942
34	08/06/2022	Gavin Jones Limited	SO0061331	LK	DUCT45	Naylor 450x450mm Duct Access Box 335mm Depth B125 Loading	3	0		ONI				-31943
35	08/06/2022	Barry Howard Homes Ltd	SO0061739	NIJ	375TWSN	375mm Twinwall Drain Seal	8	65		ONI				-31943
36	08/06/2022	Barry Howard Homes Ltd	SO0061739	NIJ	375TWCN	375mm Twinwall Pipe Coupler	2	0		ONI				-31943
37	08/06/2022	Barry Howard Homes Ltd	SO0061739	NIJ	375TDBLN	375mm BBA Twinwall Plain Ended Pipe x6m	1	0		ONI				-31943
38	08/06/2022	LNT Construction Ltd	SO0061862	NIJ	160TWBL	160mm OD TW Electrical Duct x50m Coil Black (140mm ID)	4	0		ONI				-31943
39	09/06/2022	Hannon Brothers Ltd	SO0061553	NIJ	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	5	0		ONI				-31944
40	09/06/2022	Hannon Brothers Ltd	SO0062158	NIJ	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	4	0		ONI				-31944
41	09/06/2022	Barry Howard Homes Ltd	SO0062123	NIJ	60PGD50	Perforated Gas Duct: 60mm x 50m Coil; Yellow	2	0		ONI				-31944
42	09/06/2022	Goldmax Ltd	SO0062234	LK	4VF20D	110mm PVC Drainage D/Socket Coupler	25	233						-31944
43	09/06/2022	Prestige Civil (Westbury) Engineering Ltd	SO0062139	NIJ	4VF20D	110mm PVC Drainage D/Socket Coupler	30	233						-31944
44	09/06/2022	Fenton Civil Engineering Ltd	SO0062174	LK	4VF20D	110mm PVC Drainage D/Socket Coupler	200	233						-31944
45	09/06/2022	Prestige Civil (Westbury) Engineering Ltd	SO0062110	NIJ	4VF20D	110mm PVC Drainage D/Socket Coupler	20	233						-31944
46	09/06/2022	Stave-Con Ltd	SO0062178	NIJ	450VCRA	450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown	40	0		ONI				-31944
47	09/06/2022	McCoy Contractors Ltd	SO0062153	NG	150TW30	150mm ID/ 178mm OD Twinwall Duct Bend 30deg Long Radius P/E	4	0		LS				-31944
48	10/06/2022	Foran Construction Ltd	SO0062190	NIJ	4VF20D	110mm PVC Drainage D/Socket Coupler	20	233		ONI				-31945
49	16/06/2022				4VF20D					ONI				-31949
50	17/06/2022
Jun

Cell Formulas
Range	Formula
N2:N49	N2	=NETWORKDAYS(Jun!$A2,Jun!$M2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C20:C30	Cell Value	duplicates	text	NO
E20:E30	Cell Value	duplicates	text	NO
C14:C19	Cell Value	duplicates	text	NO
E14:E19	Cell Value	duplicates	text	NO
C2:C19	Cell Value	duplicates	text	NO
C58:C1048576,C31:C48,C1:C19	Cell Value	duplicates	text	NO
C58:C1048576,C31:C48,C1:C19	Cell Value	duplicates	text	NO
C58:C1048576,C31:C48,C1	Cell Value	duplicates	text	NO
E1:E19,E58:E1048576,E31:E49	Cell Value	duplicates	text	NO

 

[Eric Penfold]

Here you go

 

[kevin9999]

Thanks Eric. OK, when I run this code:

Sub Filter_2_days_v3()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .AutoFilter 1, Format(Date, "dd/mm/yyyy"), 2, Format(Date - 1, "dd/mm/yyyy")
    End With
End Sub

I get this:
Before

filter.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Order Date	Customer Name	Order No.	Ent By	Code	Description	BO Qty.	Intact Stock Levels	Notes	Back Order Reason Code	Supplier Name	Depot	Back Order Release Date	Days to Fulfil
2	1/06/2022	T Vaughan Ltd	SO0061658	NIJ	VFFSC175	150-175mm Flexible Coupling VSC175	10	5		LS	VIP001	C
3	1/06/2022	Hannon Brothers Ltd	SO0061620	NIJ	SF27/100	80g Non-Woven GP Geotextile 4.5 x 100m Roll	1	25		ONI	DON001	C
4	1/06/2022	Oliver Curran Construction Ltd	SO0061597	NIJ	4VGRO	Bottle Gully Raising Piece x225mm High Orange	6	0		LS	PLA002	C
5	1/06/2022	JD Excavations Tyrone Ltd	SO0061618	NIJ	450VCRA	450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown	24	0		ONI	BRE002	C
6	1/06/2022	M&T Midlands Ltd	SO0061365	NIJ	375TWCN	375mm Twinwall Pipe Coupler	1	0		ONI	NAY001	C
7	1/06/2022	Elmcroft Construction Ltd	SO0061655	NIJ	225R150	225/150mm ID (172mm) D/Socket Naylor TW Drain Reducer	1	13		LS	NAY001	C
8	1/06/2022	LF Solutions Ltd	SO0061265	NIJ	10VF90	250mm S/Socket Bend 87.5deg	2	0		LS	RED002	C
9	1/06/2022	LF Solutions Ltd	SO0061265	NIJ	10VF45	250mm S/Socket Bend 45deg	1	0		LS	RED002	C
10	1/06/2022	LF Solutions Ltd	SO0061265	NIJ	10VF15	250mm S/Socket Bend 15deg	5	0		LS	RED002	C
11	1/06/2022	Hannon Brothers Ltd	SO0061616	NIJ	POLYT	Please note above duct is standard twinwall electric duct. Not compliant with ENATS 12-24 or EDF/SSE requirements.	1	0		ONI	NONE	C
12	1/06/2022	Hannon Brothers Ltd	SO0061616	NIJ	110RTWBL	110mm OD Twinwall Electrical Duct x6m; Black c/w Coupler	15	18		NSI	NAY001	C
13	1/06/2022	West Point Construction Limited	SO0061485	NIJ	100PGD50	Perforated Gas Duct: 100mm x 50m Coil; Yellow	5	0		LS	NAY001	C
14	1/06/2022	Southern Mastic Roofing Ltd	SO0061437	NIJ	33.0304	Frost Inverted Roof drain assembly - fixed dome 220mm and 150mm gravel guard, medium sump body with clamp, vertical threaded outlet 4" BSP	11	0		NSI	NONE	C
15	2/06/2022	Gtek Plant Ltd	SO0061251	NG	450TDBLN	450mm BBA Twinwall Plain Ended Pipe x6m	6	0		ONI	NAY001	C
16	2/06/2022	Carey London Ltd	SO0058403	LK	31.6517	Frost Spigot Adaptor BSP 3 to 82mm plastic	17	0		ONI	JIM001	C
17	2/06/2022	O'Halloran & O'Brien Ltd	SO0052299	SM	31.6002	Frost Extension cast iron 140mm NPSM4	27	0		ONI	HEB001	C
18	2/06/2022	O'Halloran & O'Brien Ltd	SO0052299	SM	31.1071	Frost Body S trap cast iron vertical outlet 100mm DIA supplied with 3 x 2" BSP plugged inlets	1	0		LS	HEB001	C
19	2/06/2022	Slane Construction Ltd	SO0052069	LK	31.0275.S	Frost Floor drain 178mm circular stainless steel grating for vinyl floors with medium sump -'S' trap - spigot outlet size 100mm	1	0		LS	FRO001	C
20	6/06/2022	Norcon Construction Ltd	SO0061805	NIJ	63TWOS	63mm OD Twinwall Duct x50m; Street Lighting Orange	2	0		ONI
21	6/06/2022	Flatbase Construction Services Ltd	SO0061545	NIJ	63PTNE90	63mm Puriton Pipe Elbow 90deg; Type A	3	0		LS
22	6/06/2022	Flatbase Construction Services Ltd	SO0061545	NIJ	63PTN50	Puriton Barrier Pipe: 63mm x 50m Coil; Type A SDR11	2	0		LS
23	6/06/2022	RM Contractors Ltd	SO0061733	NIJ	32PD100	32/37mm Polyethylene Duct x100m Coil; Black Printed Electric	1	0		ONI
24	6/06/2022	A D Bly Construction Ltd	SO0060761	NG	225R160	225mm Naylor TW Socket to 160mm UPVC Socket Level Invert Reducer	3	-1		ONI
25	6/06/2022	J Holland Construction Ltd	SO0061635	NIJ	110TWBL	110mm OD Twinwall Electrical Duct x50m; Black	1	0		ONI
26	6/06/2022	Norcon Construction Ltd	SO0061726	NIJ	100PGD50	Perforated Gas Duct: 100mm x 50m Coil; Yellow	3	0		ONI
27	7/06/2022	Goldmax Ltd	SO0061716	LK	PB160	158-170mm Zinc Plated Pipe Bracket Rubber Lining - M8/M10 Thread	100	0		LS
28	7/06/2022	Agetur UK Ltd	SO0061735	NIJ	63TWBL	63mm OD Twinwall Duct x50m; Black	4	0		ONI
29	7/06/2022	ALBY Associates Ltd	SO0061729	LK	160TWBL25	160mm OD Twinwall Electrical Duct x25m Coil Black (140mm ID)	4	0		ONI
30	7/06/2022	M&T Midlands Ltd	SO0061845	NIJ	300RTWBLN	300mm ID Twinwall Duct x6m; Black; P/E	1	0		NSI
31	7/06/2022	M&T Midlands Ltd	SO0061845	NIJ	110TWBL	110mm OD Twinwall Electrical Duct x50m; Black	4	0		ONI
32	7/06/2022	McCoy Contractors Ltd	SO0061917	NIJ	110TW90	110mm OD Twinwall Duct Bend Long Radius 90deg; P/E	9	0		ONI
33	7/06/2022	J Holland Construction Ltd	SO0061911	NIJ	110TW90	110mm OD Twinwall Duct Bend Long Radius 90deg; P/E	2	0		ONI
34	8/06/2022	Gavin Jones Limited	SO0061331	LK	DUCT45	Naylor 450x450mm Duct Access Box 335mm Depth B125 Loading	3	0		ONI
35	8/06/2022	Barry Howard Homes Ltd	SO0061739	NIJ	375TWSN	375mm Twinwall Drain Seal	8	65		ONI
36	8/06/2022	Barry Howard Homes Ltd	SO0061739	NIJ	375TWCN	375mm Twinwall Pipe Coupler	2	0		ONI
37	8/06/2022	Barry Howard Homes Ltd	SO0061739	NIJ	375TDBLN	375mm BBA Twinwall Plain Ended Pipe x6m	1	0		ONI
38	8/06/2022	LNT Construction Ltd	SO0061862	NIJ	160TWBL	160mm OD TW Electrical Duct x50m Coil Black (140mm ID)	4	0		ONI
39	9/06/2022	Hannon Brothers Ltd	SO0061553	NIJ	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	5	0		ONI
40	9/06/2022	Hannon Brothers Ltd	SO0062158	NIJ	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	4	0		ONI
41	9/06/2022	Barry Howard Homes Ltd	SO0062123	NIJ	60PGD50	Perforated Gas Duct: 60mm x 50m Coil; Yellow	2	0		ONI
42	9/06/2022	Goldmax Ltd	SO0062234	LK	4VF20D	110mm PVC Drainage D/Socket Coupler	25	233
43	9/06/2022	Prestige Civil (Westbury) Engineering Ltd	SO0062139	NIJ	4VF20D	110mm PVC Drainage D/Socket Coupler	30	233
44	9/06/2022	Fenton Civil Engineering Ltd	SO0062174	LK	4VF20D	110mm PVC Drainage D/Socket Coupler	200	233
45	9/06/2022	Prestige Civil (Westbury) Engineering Ltd	SO0062110	NIJ	4VF20D	110mm PVC Drainage D/Socket Coupler	20	233
46	9/06/2022	Stave-Con Ltd	SO0062178	NIJ	450VCRA	450mm Inspection Chamber Shaft 300mm Height includes seal (Adoptable SFA7) Brown	40	0		ONI
47	9/06/2022	McCoy Contractors Ltd	SO0062153	NG	150TW30	150mm ID/ 178mm OD Twinwall Duct Bend 30deg Long Radius P/E	4	0		LS
48	10/06/2022	Foran Construction Ltd	SO0062190	NIJ	4VF20D	110mm PVC Drainage D/Socket Coupler	20	233		ONI
49	16/06/2022				4VF20D					ONI
50	17/06/2022
Sheet2

After

filter.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Order Date	Customer Name	Order No.	Ent By	Code	Description	BO Qty.	Intact Stock Levels	Notes	Back Order Reason Code	Supplier Name	Depot	Back Order Release Date	Days to Fulfil
49	16/06/2022				4VF20D					ONI
50	17/06/2022
Sheet2

I'm getting both today (17th June) and yesterday (16th June)

 

[Eric Penfold]

Thanks very much all working

 

[kevin9999]

Thanks very much all working

Excellent news Eric!

 

[kevin9999]

UPDATE

My friend and fellow volunteer @Alex Blakenburg has pointed out a problem (thanks Alex) in the code so far provided – insofar as it will fail if the today/yesterday dates are single digit days. To overcome this, I’ve added a single line to the existing code to ensure the entire range is formatted to match the filter.

Sub Filter_2_days_v4()
    With ActiveSheet.Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .NumberFormat = "d/mm/yyyy"
        .AutoFilter 1, Format(Date, "d/mm/yyyy"), 2, Format(Date - 1, "d/mm/yyyy")
    End With
End Sub

 

[Eric Penfold]

Thanks that helps