vba 查找包含特定文本的第一行和最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29019237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find first and last row containing specific text
提问by BruceWayne
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
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.
如何找到包含每个单词的第一行（从顶部开始）和最后一行。Bats 从第 1 行开始，在第 6 行结束。 Fun 从第 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?
除了将每个单元格与前一个单元格进行比较并添加行变量（如果它们不同）的循环之外，还有其他更快的方法来执行此操作的想法吗？
Looking for how to do so in VBA.
寻找如何在 VBA 中执行此操作。
回答by BruceWayne
I figured it out - VBA style. I can just use find() to help out:
我想通了 - VBA 风格。我可以使用 find() 来帮忙：
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.
然后用其他词替换“bats”，它会起作用。
Edit: You may need to add the LookIn:=xlValuesqualifier too, depending on the info/data being searched.
编辑：您可能还需要添加LookIn:=xlValues限定符，具体取决于正在搜索的信息/数据。
回答by IAWeir
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.
并用您要查找的每个不同值替换“Bats”。
