Find first and last row containing specific text

Question

I have a long (2,000 + rows) list of different values. I am trying to find the first and last row for each value. As an example (note: all data is in one column):

Bats
Bats
Bats
Bats
Bats
Bats
Fun
Fun
Fun
Fun
Bases
Bases
Bases
Bases
Bases
Bases
Bases
Bases
Bases
Bases
Bases
Balls
Balls
Balls
Balls
Balls
Balls
Balls
Balls

How do I find the first row (starting at the top) and last row containing each word. "Bats" starts row 1, ends at row 6. "Fun" starts row 7, ends at row 10.

Any ideas for a quicker way to do this, other than a loop where I compare each cell to the previous, and add a row variable if they're different?

Looking for how to do so in VBA.

Answer 1

I figured it out - VBA style. I can just use find() to help out:

Dim batStartRow as Long, batEndRow as Long
With Sheets("Sheet1")
    batStartRow = .Range("A:A").Find(what:="bats", after:=.Range("A1")).Row
    batEndRow = .Range("A:A").Find(what:="bats",after:=.Range("A1"), searchdirection:=xlPrevious).Row
End With

Then replace "bats" with the other words, and it'll work.

Edit: You may need to add the LookIn:=xlValues qualifier too, depending on the info/data being searched.

Answer 2

if the values are already grouped you can use the following to find the first Row occurrence

=MATCH("Bats",A:A,0)

and this to find the last Row occurrence

=(MATCH("Bats",A:A,0)+(COUNTIF(A:A,"Bats"))-1)    

and substitute "Bats" with each distinct Value you want to look up.