10

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
  • 22,923
  • 15
  • 65
  • 110
  • I found the following post most helpful, using the After:= paramerter https://stackoverflow.com/questions/28333965/use-range-find-method-in-a-specific-column – gimmegimme Sep 06 '18 at 00:46

2 Answers2

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 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? – marcoresk Apr 23 '17 at 08:38
  • @marcoresk what do you mean? It returned as a range variable instead of number? – BruceWayne Apr 23 '17 at 18:14
  • 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? – marcoresk Apr 23 '17 at 20:19
  • @marcoresk - Are you using multiple sheets? The `after:=Range()` would also need the `Sheet1.` before it. Does that fix it? – BruceWayne Apr 23 '17 at 21:51
  • 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. – marcoresk Apr 24 '17 at 07:45
  • @marcoresk in the formula, after `after:=Range("A1")` add `, LookIn:=xlValues` – BruceWayne Apr 24 '17 at 14:26
  • Thank you!. Guide tells me about LookIn = Values whitout "x". – marcoresk Apr 24 '17 at 16:25
10

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.

IAWeir
  • 266
  • 1
  • 6