VBA-删除所有行,直到找到请求的值

最后发布: 2018-08-23 10:48:23


问题

你能告诉我,我在下面的代码中我该怎么做?

我有可变数据量的excel文件。 数据表已拆分,我需要删除其第二部分。 数据的第二部分以A列中的“不在Ico贷款工具中的一方或双方”开头。因此,基本上,我认为我会从后面开始查找并删除每一行,直到找到包含搜索短语的单元格为止,然后它会停止。

该代码删除了直到A3的所有内容,包括数据的第一部分。 请问您有什么建议吗?

Private Sub Remove_part2()

Dim i As Long
Dim LastRow As Long
Dim r As Range

With Worksheets("Hyperion Data (Original)")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set r = Range("A3", "A" & Rows.Count).Find(What:="One or both Parties Not in Ico Loan Facility", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)

For i = LastRow To 3 Step -1

If Not r Is Nothing Then
  ActiveSheet.Rows(i).EntireRow.Delete
End If
Next i

End With
End Sub
excel vba excel-vba find
回答

怎么样:

Private Sub Remove_part2()
Dim i As Long
Dim LastRow As Long
Dim r As Range
Dim ws As Worksheet: Set ws = Worksheets("Hyperion Data (Original)")

With ws
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set r = .Range("A3", "A" & .Rows.Count).Find(What:="One or both Parties Not in Ico Loan Facility", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
    If Not r Is Nothing Then
        For i = LastRow To r.Row Step -1
            ws.Rows(i).EntireRow.Delete
        Next i
    Else
        MsgBox "Criteria Not Found!", vbInformation
    End If
End With
End Sub