Find last date in column with mixed data

[Luke777]

Hi,

Column A is part of a larger dataset, it includes non-repeated dates and repeating strings. For example, 01/01/2024, followed by names (info in columns to right), 02/01/2024, followed by the same names with info on the right. Sounds a little messy, but presentation-wise, its about the best way to format the data.

Anyway.. I'm using the following code to find the biggest date (its always in order)

Max_date = Application.WorksheetFunction.Max(Columns("A"))

Which works fine.. only I want the cell address (preferably just the row number but I can work that bit out myself I think), not the value itself

The only solution I can think of is then pass Max date through .Find? I'm not sure if that's the best way though

Any suggestions?

Thanks

 

[SanjayGMusafir]

only I want the cell address (preferably just the row number but I can work that bit out myself I think), not the value itself

Check this and revert - This worked for me, but I didn't have mix data.

=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A:A),4,1)

 

[Luke777]

Check this and revert - This worked for me, but I didn't have mix data.

=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A:A),4,1)

Sorry, I should have specified I'm after a VBA solution

 

[Luke777]

Hi,

Column A is part of a larger dataset, it includes non-repeated dates and repeating strings. For example, 01/01/2024, followed by names (info in columns to right), 02/01/2024, followed by the same names with info on the right. Sounds a little messy, but presentation-wise, its about the best way to format the data.

Anyway.. I'm using the following code to find the biggest date (its always in order)

Max_date = Application.WorksheetFunction.Max(Columns("A"))

Which works fine.. only I want the cell address (preferably just the row number but I can work that bit out myself I think), not the value itself

The only solution I can think of is then pass Max date through .Find? I'm not sure if that's the best way though

Any suggestions?

Thanks

Solved with the following

lDate = Sheet1.Range("A1:A" & lRow).Find(What:=Max_date, LookIn:=xlValues).Address

Would appreciate any feedback on this solution