macro filter column for most recent date

[flymo]

Helo,
I have a macro running to filter a series of values and works fine so
far. However, I need to filter a column of dates and only need to
select records with the most recent date (I can't make the field =
NOW() to get current date as the records may include today but also
may be a few days old.

Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="HHP"
Selection.AutoFilter Field:=3, Criteria1:="Open"
Selection.AutoFilter Field:=2, Criteria1:="2007/06/14 21:00" '
this is the most current right now - so may change day to day
Range("A171:AV171").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Selection.Columns.AutoFit


Many thanks
John

 

[Bernie Deitrick]

John,

Use this in place of your field 2 code:

Selection.AutoFilter Field:=2, Criteria1:="1", Operator:=xlTop10Items

HTH,
Bernie
MS Excel MVP

 

[Guest]

Cells.Select
Dim dt as Date
' the next line assumes the date column is in the column to the right of
the upper
' left corner of the selection. Adjust to suit.
dt = clng(Application.Max(selection(1).offset(0,1).EntireColumn))
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="HHP"
Selection.AutoFilter Field:=3, Criteria1:="Open"
Selection.AutoFilter Field:=2, Criteria1:=">=" & format(dt,"yyyy/mm/dd")
this is the most current right now - so may change day to day
Range("A171:AV171").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Selection.Columns.AutoFit

 

[flymo]

John,

Use this in place of your field 2 code:

Selection.AutoFilter Field:=2, Criteria1:="1", Operator:=xlTop10Items

HTH,
Bernie
MS Excel MVP

Bernie,
Worked like a charm.
Many Thanks

John