Macro code to filter for the end date of the previous month

**yipdog** · 03-31-2020, 12:34 PM

Hi Everyone

I am relatively new to VBA

I have a table with around 20 columns, one of them displays the latest available date of the performance data, because the performance data for the structured derivatives I am measuring do not usually get updated all in one go as of the same date, I want to be able to use a macro to filter out all the up to date structured derivatives, which is filtered by the end date of the previous month.

I know the excel formula is =EOMONTH(TODAY(),-1)
How can I design a macro with the formula above and assign a button to it.

Thanks!!

**Bernie Deitrick** · 03-31-2020, 01:17 PM

Use code like this, for dates in column B (the 2 in Array(2, means the second column in the filtered range.

Please Login or Register  to view this content.

You can also record a macro, and replace the selected date (like "2/29/2020") with DateSerial(Year(Date), Month(Date), 0)

**yipdog** · 03-31-2020, 07:45 PM

Attachment 669998

I actually have a table in place, for some reason, an error is popping up after I try to incorporate your macro, would you be able to help!

**Bernie Deitrick** · 03-31-2020, 09:37 PM

Tables require different code, along the lines of

Please Login or Register  to view this content.

Really, the thing to do is to record a macro where you select the date column and filter for any single date, then stop the recording. The replace the date string in the code with

Please Login or Register  to view this content.

Macro code to filter for the end date of the previous month

LinkBack

Thread Tools

Rate This Thread

Display

Macro code to filter for the end date of the previous month

Re: Macro code to filter for the end date of the previous month

Re: Macro code to filter for the end date of the previous month

Re: Macro code to filter for the end date of the previous month

Thread Information

Users Browsing this Thread

Similar Threads

Filter Pivot by Previous Month

[SOLVED] Macro Filter Month by Date

Macro for applying filter for previous month's data

[SOLVED] Filter for the previous month only - Pivot VBA

VBA code adjustment to include only current month and previous month automatically.

[SOLVED] Date formula with text in same cell but using previous month not current month

[SOLVED] Code to auto filter three month segments of data from today's date

Bookmarks

Bookmarks

Posting Permissions