Filtering by "yesterday's" date

[rharri1972]

Hello! I have a an open order report that I need to filter by "yesterday's" date or today's date (-) 1. This will be filtered in the "order date" column which is always column 6.

Obviously being an open orders report, this column has several dates in it and I need to specifically filter JUST the dates of orders that were processed the previous day.

Can anyone help me with VBA on this please?

Thanks in advance.

 

[etaf]

do you specifically need a VBA solution
=IFERROR(IF(INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=TODAY()-1,ROW($A$2:$A$26)),ROW(1:1))-1,1)=0,””,INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=TODAY()-1,ROW($A$2:$A$26)),ROW(1:1))-1,1)),"")

Extract list before today.xlsx
	A	B	C	D	E	F
1	Vehicle No.	Expire Date				LIST
2	675	1/31/19				675a
3	790a	10/28/19				790b
4	675a	2/14/23				675b
5	790b	2/14/23
6	675b	2/14/23
7	790c	2/15/23
8	675c	2/15/23
9	675d	2/15/23
10	675e	2/15/23
11	675f	2/15/23
12	12a	2/15/23
13	4567	2/15/23
14	1214	2/15/23
Sheet1

Cell Formulas
Range	Formula
B4:B6	B4	=TODAY()-1
B7:B14	B7	=TODAY()
F2:F14	F2	=IFERROR(IF(INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=TODAY()-1,ROW($A$2:$A$26)),ROW(1:1))-1,1)=0,””,INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=TODAY()-1,ROW($A$2:$A$26)),ROW(1:1))-1,1)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B14	Expression	=$B1=TODAY()-1	text	YES

 

[Flora2021]

I think I found a solution before. I will see if i can find and repost.

 

[kevin9999]

Try the following:

Option Explicit
Sub Yesterday()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    
    With ws.Range("A1").CurrentRegion
        .AutoFilter 6, 2, 11
    End With
End Sub

Before (it's the 17th May here now)

Book1
	A	B	C	D	E	F
1	HDR1	HDR2	HDR3	HDR4	HDR5	Order Date
2	data	data	data	data	data	15/05/2023
3	data	data	data	data	data	15/05/2023
4	data	data	data	data	data	15/05/2023
5	data	data	data	data	data	16/05/2023
6	data	data	data	data	data	16/05/2023
7	data	data	data	data	data	16/05/2023
8	data	data	data	data	data	17/05/2023
9	data	data	data	data	data	18/05/2023
10	data	data	data	data	data	19/05/2023
11	data	data	data	data	data	20/05/2023
Sheet1

After:

Book1
	A	B	C	D	E	F
1	HDR1	HDR2	HDR3	HDR4	HDR5	Order Date
5	data	data	data	data	data	16/05/2023
6	data	data	data	data	data	16/05/2023
7	data	data	data	data	data	16/05/2023
12
Sheet1