Find last date

[mmetzinger]

Ok, this should be an easy one. I need to figure out how to get the find function to activate the last cell with the found data. The task is to find the last date in a column of dates that may have duplicates. So if you just use the find method it stops at the first cell it finds with the date.

Columns("U").Find(What:=EndDate, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate

I need to find the last cell in the column with that date. I cannot resort the data to make it show up first because I am establishing a row range to copy to a new sheet.

Any help?

 

[njimack]

Sub test()
Dim Last_Row As Long, n As Long

Last_Row = Range("U" & Rows.Count).End(xlUp).Row

For n = Last_Row To 2 Step -1
    If Cells(n, 21) = WorksheetFunction.Max(Range("U:U")) Then
        Cells(n, 21).Select
        Exit Sub
    End If
Next n
End Sub

 

[AlphaFrog]

Columns("U").Find(What:=EndDate,[COLOR="Red"] After:=Range("U1")[/COLOR], _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
[COLOR="Red"]SearchDirection:=xlPrevious[/COLOR], MatchCase:=False, SearchFormat:=False).Activate