CODEWITHSUNDEEP
exceldatevba
Will
Will

Reputation: 315

Can I filter data to show current month in VBA?

I'm working with tabular data in Excel that shows my personal budget transaction ledger from the past few months (columns include date of the transaction, budget category, transaction amount, etc.).

I'd love to create a script that will filter this data to only show the current month's transaction data, whatever month that might happen to be. By recording a simple macro, I can see how Excel writes a script that filters the date column (in the below example, column 3), for the month of July: 

ActiveSheet.Range("A:K").AutoFilter Field:=3, Operator:= _
    xlFilterValues, Criteria2:=Array(1, "7/29/2017")

To use this formula in the future, I obviously don't want to hardcode "7/29/2017" into the script, but instead use something like the =MONTH(TODAY()) function combo in Excel so that it only selects transactions from the current month. But every time I try and run the above script with the TODAY function, I get an error ("Compile error: Sub or Function not defined").

Do I need to create a variable to hold the value of the month first? Or use a different formula within the script? I'm new to scripting with VBA so thanks for the help!

Upvotes: 2

Views: 6845

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Range("A:K").AutoFilter Field:=3, Operator:=xlAnd, _
 Criteria1:=">" & Application.EoMonth(Now, -1), _
 Criteria2:="<=" & Application.EoMonth(Now, 0)

  • EoMonth(Now, -1) marks the end of last month

  • EoMonth(Now, 0) marks the end of the current month

Upvotes: 2

Related Questions