Searching from Bottom Up or a Find Last command?

[Guest]

Howdy

I have a list of events that are sorted in date/time order. Basically each time an item gets added to the list, it gets added at the bottom. However I need a way of finding the LAST occurrance of any event. Is there a way to search starting at the bottom so it would find the last item? Or any way to say Find the Last occurance of an item or something like that

Thanks.

 

[Erin]

The 6th argument for the Find method is searchorder. The
default is xlNext. If the active cell of your lookup
range is at the top, try changing that argument to
xlPrevious. That will search backwards.

HTH

Erin

-----Original Message-----
Howdy,

I have a list of events that are sorted in date/time

order. Basically each time an item gets added to the
list, it gets added at the bottom. However I need a way
of finding the LAST occurrance of any event. Is there a
way to search starting at the bottom so it would find the
last item? Or any way to say Find the Last occurance of
an item or something like that?

 

[Dave Peterson]

If you start your search at the top of the column and tell excel to look for the
previous cell, then it'll go to the bottom and start looking:

Option Explicit
Sub testme()
Dim FoundCell As Range
Dim LookFor As String

LookFor = "asdf"
With ActiveCell.EntireColumn
Set FoundCell = .Cells.Find(What:=LookFor, _
After:=.Cells(1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False)
End With

If FoundCell Is Nothing Then
'not found
Else
FoundCell.Activate
End If

End Sub

If you're doing it manually, you can get this same effect by starting in row 1.
But instead of just hitting the "find next" button, hold the shift key while you
click on the "Find Next" button.

 

[Dave Peterson]

For the manual method:

Select the column first--that limits the search to just that column. Then
shift-click FindNext.