查找包含特定文本的第一行和最后一行 [英] Find first and last row containing specific text

问题描述

我有一长串(2,000多行)不同的值.我试图找到每个值的第一行和最后一行.例如(注意:所有数据都在一列中):

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

如何找到包含每个单词的第一行(从顶部开始)和最后一行.蝙蝠从第1行开始，在第6行结束.Fun从第7行开始，在第10行结束.

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?

寻找在VBA中的操作方法.

Looking for how to do so in VBA.

推荐答案

我知道了-VBA样式.我可以使用find()来解决问题:

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

然后用其他词替换"bats"，它将起作用.

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

您可能还需要添加LookIn:=xlValues限定词，具体取决于要搜索的信息/数据.

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

这篇关于查找包含特定文本的第一行和最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！