VBA - 查找包含特定文本的第一行和最后一行

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):

我有一个很长的(2,000 +行)不同值列表。我试图找到每个值的第一行和最后一行。作为示例(注意:所有数据都在一列中):

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

So, I was wondering what the quickest way to find the FIRST row (starting at the top) and LAST row containing each word is. So, bats starts row 1, ends at row 6. Fun starts row 7, ends at row 10.

所以,我想知道找到第一行(从顶部开始)和包含每个单词的最后一行的最快方法是什么。因此,蝙蝠从第1行开始,在第6行结束。乐趣从第7行开始,在第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?

除了我将每个单元格与之前的单元格进行比较的循环之外,还有更快捷的方法可以实现这一点,如果它们不同,可以添加行变量吗?

Thanks so much for any advice/help!

非常感谢任何建议/帮助!

edit: looking for how to do so in VBA.

编辑:在VBA中查找如何执行此操作。

2 个解决方案

#1

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

我想通了 - VBA风格。我可以使用find()来帮助:

Dim batStartRow as Integer, batEndRow as Integer
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.

然后用其他单词替换“bats”,它会起作用。

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

编辑:您可能还需要添加LookIn:= xlValues限定符,具体取决于搜索的信息/数据。

#2

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

如果值已经分组,您可以使用以下内容查找第一个Row事件

=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.

并用你想要查找的每个不同的值替换“蝙蝠”。

#1