Find Last Instance of "Text" in a column

  • Thread starter Thread starter Chris Premo
  • Start date Start date
C

Chris Premo

I have a sorted column of text that I want to find the last instance
where the text begins with an asterisk ("*"). I can use this code to
do the search, but I would have to know how many times to "continue the
search". Also, the "~*" will also find any word with the asterisk in
it, while I only want those words with the asterisk as the first
character.


Columns("C:C").Select
Selection.Find(What:="~*", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate




--
 
I have a sorted column of text that I want to find the last instance
where the text begins with an asterisk ("*"). I can use this code to
do the search, but I would have to know how many times to "continue the
search". Also, the "~*" will also find any word with the asterisk in
it, while I only want those words with the asterisk as the first
character.


Columns("C:C").Select
Selection.Find(What:="~*", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate
Click to expand...


If by "last instance" you mean the instance in the highest numbered row in
Column C, and if you mean that the first character in the cell should be an
asterisk, then try this:

==========================
Sub LastAsterisk()
Dim r As Range
Set r = Columns(3).Find(what:="~**", _
after:=Range("C1"), _
lookat:=xlWhole, _
searchdirection:=xlPrevious)

Debug.Print r.Address
End Sub
====================

--ron
 
Back
Top