首页 文章

VBA - 查找重复文本的第一行和最后一行

提问于
浏览 次
-1

我的问题类似于这个VBA - Find first and last row containing specific text但我需要一些调整 . 我的A列值仅为 Delete ， Delete end 和 - （破折号），我需要获取 Delete 所在的第一行和 Delete end 所在的最后一行 .

Rows    Column A
1       Delete
2       Delete
3       Delete end
4       -
5       -
6       -
7       -
8       Delete
9       Delete end

编辑：我想提取行1和3，以及8和9，而不是像A1：A3这样的范围 . 我该怎么办？

excel vba excel-vba

1 回答

  • 1

    希望这是你想要的 .

    假设您的数据以A2开头..

    Sub test()
    Dim lastrow As Long, i As Long
    Dim startaddress As String, endaddress As String, alladdress As String
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
        If Cells(i, 1) = "Delete" Then
            startaddress = Cells(i, 1).Address
            Do While Cells(i, 1) <> "Delete end"
                i = i + 1
            Loop
            endaddress = Cells(i, 1).Address
            If alladdress <> "" Then
                alladdress = alladdress & ", " & startaddress & ":" & endaddress
            Else
                alladdress = startaddress & ":" & endaddress
            End If
        End If
    Next i
    Cells(2, 2) = alladdress
End Sub

    enter image description here

    回复于

相关问题