View Full Version : [SOLVED:] selecting the newest date
Omaradel
08-29-2013, 08:45 AM
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
08-31-2013, 06:34 AM
You can use WorksheetFunction.Max(Range) to find the latest date and then use that as the criteria for the filter.
Paul_Hossler
08-31-2013, 06:54 AM
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
08-31-2013, 12:23 PM
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
08-31-2013, 04:00 PM
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"))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.