Last Row in Dataset? VBA

[Phlip183]

Hi, Im looking for some VBA code that will find the last row that I specify. For example:

There are 4 A's and 5 B's in Coulmn A (total of 9 rows). How do i find the last A before the B's start? The code would have to be a variable so i can enter in what ever type of text i wanted.

Thanks for the help!

 

[VoG]

Try like this

Sub test()
Dim s As Variant, LR As Long
s = InputBox("Enter search value")
LR = Columns("A").Find(What:=s, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
MsgBox LR
End Sub

 

[Phlip183]

Thanks for the quick response Vog, but not exactly. I dont really want a text box to show up so i (as the user) can find the specific value, i want to edit it in the code myself so it can be part of a fully automated process i am working on.

And also, the other problem is that once i enter in the text i need to search on, it doesnt select the last value/text, it just tells there are 4 of them. I need it to select the last A.

 

[VoG]

I don't see why you need to select the cell (selecting is seldom necessary) but try like this

Sub test()
Dim s As Variant, LR As Long
s = "a"
LR = Columns("A").Find(What:=s, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A" & LR).Select
End Sub

 

[Phlip183]

Thats perfect! Thanks alot for all the help!