Command line to filter on the last date
 

In a macro what is the code to filter on the last date in column of dates
without specifiying an actual date? (Note: The last date will be different
each day, e.g. today the last date will be 05/12/08, tommorrow the last date
in the column will be 05/13/08, etc.)

Command line to filter on the last date
 

It sounds like the last date will always be yesterday--but will that be true on
Sundays and Mondays???

Or should it just show you the latest date used in that column?

This expects you to have applied the filter arrows to some range on the
worksheet. And it filters on the first column of that autofilter range--and it
filters to show the max date--not yesterdays:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myStr As String
Dim myDate As Date

Set wks = Worksheets("sheet1")

With wks
If .FilterMode Then
.ShowAllData
End If

With .AutoFilter.Range.Columns(1)
myDate = Application.Max(.Cells)
myStr = Format(myDate, "mm/dd/yyyy")
.AutoFilter Field:=1, Criteria1:="=" & myStr, _
Operator:=xlAnd, Criteria2:="<=" & myStr
End With
End With
End Sub

myDate = Application.Max(.Cells)
could be replaced with:
myDate = date - 1
(if you really _always_ wanted yesterday's date.

mulehead wrote:

In a macro what is the code to filter on the last date in column of dates
without specifiying an actual date? (Note: The last date will be different
each day, e.g. today the last date will be 05/12/08, tommorrow the last date
in the column will be 05/13/08, etc.)

--

Dave Peterson

Command line to filter on the last date
 

Dave the upper part was exactly what I needed, it works perfectly. Thank you.

"Dave Peterson" wrote:

It sounds like the last date will always be yesterday--but will that be true on
Sundays and Mondays???

Or should it just show you the latest date used in that column?

This expects you to have applied the filter arrows to some range on the
worksheet. And it filters on the first column of that autofilter range--and it
filters to show the max date--not yesterdays:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myStr As String
Dim myDate As Date

Set wks = Worksheets("sheet1")

With wks
If .FilterMode Then
.ShowAllData
End If

With .AutoFilter.Range.Columns(1)
myDate = Application.Max(.Cells)
myStr = Format(myDate, "mm/dd/yyyy")
.AutoFilter Field:=1, Criteria1:="=" & myStr, _
Operator:=xlAnd, Criteria2:="<=" & myStr
End With
End With
End Sub

myDate = Application.Max(.Cells)
could be replaced with:
myDate = date - 1
(if you really _always_ wanted yesterday's date.

mulehead wrote:

In a macro what is the code to filter on the last date in column of dates
without specifiying an actual date? (Note: The last date will be different
each day, e.g. today the last date will be 05/12/08, tommorrow the last date
in the column will be 05/13/08, etc.)

--

Dave Peterson