VBA to find the last occurrence of a word and select that word

[adamolivier]

I am trying to use the Find function to find the last location that a word is used in column A. Below is my current code. I have tried various other versions and methods, but cannot seem to figure this out.

Sub Find()


Range("a10000").End(xlUp).Select
Range("a:a").Find("Count").Select




End Sub

Any suggestions?

 

[Fluff]

How about

Sub FindLast()
   Dim Fnd As Range
   Set Fnd = Range("A:A").find("count", , , xlPart, xlByRows, xlPrevious, False, , False)
End Sub

 

[adamolivier]

How about

Sub FindLast()
   Dim Fnd As Range
   Set Fnd = Range("A:A").find("count", , , xlPart, xlByRows, xlPrevious, False, , False)
End Sub

That worked.

How would I expand this to select all rows from the last occurrence of "count" to the last occurrence of "totals"?

Thanks

 

[Fluff]

Like

Sub FindLast()
   Dim Fnd1 As Range, Fnd2 As Range
   Set Fnd1 = Range("A:A").find("count", , , xlPart, xlByRows, xlPrevious, False, , False)
   Set Fnd2 = Range("A:A").find("totals", , , xlPart, xlByRows, xlPrevious, False, , False)
   Range(Fnd1, Fnd2).EntireRow.Select
End Sub

 

[adamolivier]

That did work.

Here is what I have now:

Sub FindLast()
   Dim Fnd1 As Range, Fnd2 As Range
   Set Fnd2 = Range("A:A").Find("totals", , , xlPart, xlByRows, xlPrevious, False, , False)
   Set Fnd1 = Range("A:A").Find("count", , , xlPart, xlByRows, xlPrevious, False, , False)
   Range(Fnd1, Fnd2).EntireRow.Select
   Selection.Copy
   ActiveCell.Select
End Sub

I am trying to have the rows selected, copied, then paste 3 rows below the word "totals". I have tried doing it a couple of ways, but am having a hard time getting it to work. The above code selects the active cell, but that is the word "count".

Thanks for all of your help.

 

[Fluff]

How about

Sub FindLast()
   Dim Fnd1 As Range, Fnd2 As Range
   Set Fnd1 = Range("A:A").find("count", , , xlPart, xlByRows, xlPrevious, False, , False)
   Set Fnd2 = Range("A:A").find("totals", , , xlPart, xlByRows, xlPrevious, False, , False)
   Range(Fnd1, Fnd2).EntireRow.Copy Fnd2.Offset(3)
End Sub