selecting the newest date

[Omaradel]

hi
i am new to VBA and i am trying to write a VBA macro that applies a filter on a big excel sheet and selects always the newest date.
the receive the sheet on daily basis so the date is being changed daily, and i couldn't figure our how to make the VBA selects always the newest date.
thanks in advance.

[Teeroy]

You can use WorksheetFunction.Max(Range) to find the latest date and then use that as the criteria for the filter.

[Paul_Hossler]

You can use Filter Top10 operator, and set it to only return 1 (which would be the largest = latest date)




Sub Macro1()
Range("A1:B17").Select
Selection.AutoFilter Field:=1, Criteria1:=1, Operator:=xlTop10
End Sub


Paul

[Omaradel]

hi
this is the first thing i tried " WorksheetFunction.Max(Range) ", but it didn't work. maybe i missed something somewhere.
i tried the solution in the 2nd reply (Paul's reply) and it works fine.
thanks all.

[Teeroy]

Hi Omaradel,

Paul's response has the solution you need. For some reason i was only thinking of using Advanced Filter, not Autofilter :dunno.
In the method I posted you need to specify the range (which I guess I didn't make clear) to get the maximum of. For example if you want to get the maximum of cells A1:A15 and assign it to a variable you'd use



x = WorksheetFunction.Max(Range("A1:A15"))