Solved Filter on past one month date

[suryu]

Working on lookup main file data from another workbook data contains order date

->lookup is only for last one month data , want to filter Order Date column for the past one month date format(ex- 06/10/2020 to 07/11/2020)

 

[suryu]

Hi anybody please help me on this:

i have column name Orderdate and i want to apply filter from last month till yesterday date
if today date is 07/18/20 then it should filter from 06/18/20 to 07/17/20


confused how to do autoflter

 

[suryu]

Hi anybody please help me on this:

i have column name Orderdate and i want to apply filter from last month till yesterday date
if today date is 07/18/20 then it should filter from 06/18/20 to 07/17/20


confused how to do autoflter

 

[suryu]

Hi @arnelgp , could you please help me on this

thank you

 

[arnelgp]

can you use Custom Filter to your column.
doesn't require any VBA.

 

[Gasman]

How to Filter for Dates with VBA Macros in Excel - Excel Campus

Learn how to apply date filters with VBA. Includes examples for filtering for a range between two dates, date groups from filter drop-down list, dynamic dates in period, etc.

www.excelcampus.com

 

[suryu]

can you use Custom Filter to your column.
doesn't require any VBA.

I am working on project which should open workbook and filter column on the given criteria using vba

thank you

 

[Isaac]

What code have you tried so far after reading through Gasman's suggested article?

 

[arnelgp]

Private Sub Workbook_Open()
    'replace sheet1 with correct sheet to filter
    Sheet1.Select
    'replace Range("A1") and Range("A$1") with correct column to filter
    Range("A1").Select
    Selection.AutoFilter
    Range("$A$1").AutoFilter Field:=1, Criteria1:=">=" & VBA.Format(DateAdd("m", -1, Date), "m/d/yyyy"), Operator _
        :=xlAnd, Criteria2:="<" & VBA.Format(Date, "m/d/yyyy"), SubField:=0

End Sub

 

[suryu]

Private Sub Workbook_Open()
    'replace sheet1 with correct sheet to filter
    Sheet1.Select
    'replace Range("A1") and Range("A$1") with correct column to filter
    Range("A1").Select
    Selection.AutoFilter
    Range("$A$1").AutoFilter Field:=1, Criteria1:=">=" & VBA.Format(DateAdd("m", -1, Date), "m/d/yyyy"), Operator _
        :=xlAnd, Criteria2:="<" & VBA.Format(Date, "m/d/yyyy"), SubField:=0

End Sub

After removing SubField:=0, It is working fine ...thank you