Finding maximum day number in column

[bukimi]

I have a column with dates. Usually it consists of dates only within one month, but very rarely it has also 1st day of the next month.
I need to find maximum (latest) day number to use it in many different places in my VBA code.

Currently it looks like this:

FindLastDay = Day(WorksheetFunction.Max(Worksheets("Sales").Columns(11)))

It works really fine, except for the fact that if all dates are from May and one date is 1st June, then "FindLastDay" will give me result 1 (as it takes latest day from all dates into consideration, which is 1st June as source).

How can I fix it to take highest number of day, regardless of month in the date?
Thank you in advance for your help!

 

[AlanY]

may be

FindLastDay = Max(WorksheetFunction.Day(Worksheets("Sales").Columns(11)))

 

[bukimi]

Sorry, doesn't work.

For your code it gives "Sub or Function not defined".

I tried adding WorksheetFunction. before Max, but then it gives error "Object doesn't support this property or method".

 

[Fluff]

How about

   With Range("L2", Range("L" & Rows.Count).End(xlUp))
      FindLastDay = Evaluate("=DAY(MAX(DAY(" & .Address & ")))")
   End With

 

[bukimi]

It works!
Now my code will work for all days, even 1st day of another month

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback