CODEWITHSUNDEEP
excelvbadatefilter
Saransh
Saransh

Reputation: 11

Date Filter - Month & Year

I want to filter a date column basis 1. Year 2. Month

The Year & Month are dynamic fields and I've saved their values in the macro as YEAR - yr Month - mnth

Is there a way for the macro to simply filter using these conditions without inserting additional columns ?

enter image description here

Sub testcode()

Range("a1:a200").Select

yr = 2019
mnth = Feb

ActiveSheet.Range("$A$1:$A$3200").AutoFilter Field:=1, Criteria1:=yr

End Sub

Upvotes: 1

Views: 2573

Answers (2)

simple-solution
simple-solution

Reputation: 1139

If you record the setting of an autofilter of the date value you get:

Sub DateFiter_Recorded()
'
' DateFiter_Recorded Macro
'
    Columns("C:C").ColumnWidth = 16.29
    ActiveSheet.Range("A1:A3200").AutoFilter Field:=1, Operator:= _
        xlFilterValues, criteria2:=Array(1, "2/4/2019")
    ActiveWindow.SmallScroll Down:=153
    Range("C194").Select
End Sub

Based on this is easy to code and test a routine to set a filter:

Option Explicit

Sub SetDateValueRange()
    ActiveWorkbook.Names.Add Name:="DateValRange",  RefersTo:="=Sheet1!A1:A3200"
End Sub

Sub DateFiter_JAN_2019()
    ActiveSheet.Range("DateValRange").AutoFilter Field:=1, Operator:= _
        xlFilterValues, criteria2:=Array(1, "1/31/2019")
End Sub

Sub DateFiter_FEB_2019()
    Dim criteria2value
    criteria2value = Array(1, "2/15/2019")
    ActiveSheet.Range("DateValRange").AutoFilter Field:=1, Operator:= _
        xlFilterValues, criteria2:=criteria2value
End Sub

Sub DateFiter_AUG_2019()
    Dim criteria2value
    criteria2value = Array(1, "8/1/2018")
    ActiveSheet.Range("DateValRange").AutoFilter Field:=1, Operator:= _
        xlFilterValues, criteria2:=criteria2value
End Sub

Upvotes: 0

user11156232
user11156232

Reputation:

Set up a date range using Criteria1 and Criteria2.

dim dt as long

dt = datevalue("1 " & mnth & ", " & yr)

with activesheet
  with .range(.cells(1, "A"), .cells(.rows.count, "A").end(xlup))
    .autofilter field:=1, criteria1:=">="&dt, operator:=xland, _
                criteria2:="<" & dateserial(year(dt), month(dt)+1, 1)
  end with
end with

Upvotes: 1

Related Questions