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.

BruceWayne · Accepted Answer · 2019-05-08 16:52:01Z

20

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.

edited May 8, 2019 at 16:52

answered Mar 12, 2015 at 20:25

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

marcoresk Over a year ago

I thought that this elegant solution should give me the row number in batStartRow. I adapted it to my macro (just added the sheet.Range qualification and changing the target of reasearch). But the variables gives me the target, not the first/last row in which it is. May I ask you a clarification?

BruceWayne Over a year ago

@marcoresk what do you mean? It returned as a range variable instead of number?

marcoresk Over a year ago

for example, StartRow = Sheet1.Range("A:A").Find(what:="2016", after:=Range("A1")).Row returns me 2016 instead of the number of the first row in column A where 2016 appears. Maybe it does not work with numbers?

BruceWayne Over a year ago

@marcoresk - Are you using multiple sheets? The after:=Range() would also need the Sheet1. before it. Does that fix it?

marcoresk Over a year ago

Unfortunately not. The problem seems to be that I'm looking for a number (a year, result of a formula YEAR), because with text all works wonderfully. Also, setting cells format as "general" does not solve the problem. Even when I look for a number not in the cells for sure (there are only years 2015 - 2017, trying to look for 2018) the macro still returns "2018". When I look for 2016 (about 2000 rows contains "2016" as the year) the result is 2016, not the number of the first/last row.

|

IAWeir · Accepted Answer · 2015-03-12 20:22:03Z

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.

Ah, thanks - I was looking for a VBA way to do so (should have been more clear), but have figured it out.

Collectives™ on Stack Overflow

Find first and last row containing specific text

2 Answers 2

7 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Linked

Related