VBA for last day of previous month

[LeonardH]

Hello Everyone,

I'm working on VBA where I am using autofilter to sort data. In cell "A5" I have the formula =EOMONTH(TODAY(),0). This brings up the end of the current month. In my code for the filter, I need to be able to reference the value in that cell and get the last day of the month prior to whatever date is in that cell.

ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=34, Criteria1:= ....

Thanks!

 

[mrshl9898]

Try this:

Sub finddate()

Dim mydate As Date

mydate = Range("A1")

EoLM = DateSerial(Year(mydate), Month(mydate), 1) - 1


End Sub

 

[davidausten]

mrs beat me to it. mine was a slightly different version.