PDA

View Full Version : Filter Dates to previous month



hfarr57
05-17-2017, 07:19 AM
I am trying to filter my data so it will pull from the beginning of the year though the end of the previous month. So may 2017 I want data from 1/1/17 through the end of April, but on June 1st I want it to add in the May data.



Sub date_range()

Dim StartDate As Date
Dim EndDate As Date
Dim EndDate As Long

StartDate = DateSerial(Year(2017), Month(1), Day (1))
EndDate = DateSerial(Year(Now), Month(Now - 1), Day (last))




End Sub

mdmackillop
05-17-2017, 07:30 AM
Sub date_range()


Dim StartDate As Date
Dim EndDate As Date


StartDate = DateSerial(2017, 1, 1)
EndDate = Application.EoMonth(Date, -1)
End Sub

hfarr57
05-17-2017, 08:32 AM
Thank you! I knew there was bound to be an easy way to do it and just could not figure it out.

hfarr57
05-17-2017, 01:35 PM
This actually is not working. It is not finding any dates. I'm attaching the spreadsheet I'm looking at. Column I.

mdmackillop
05-17-2017, 03:30 PM
Sub Test()
Dim StartDate As Date
Dim EndDate As Date

StartDate = CDate(InputBox("Enter start date"))
EndDate = CDate(Application.EoMonth(Date, -1))

ActiveSheet.ListObjects("Table_Purchase_Receipts_for_OTD").Range.AutoFilter _
Field:=9, Criteria1:=">=" & CDbl(StartDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(EndDate)
End Sub