excel - 如果单元格包含字符串，Excel VBA删除行

Question

我有每周大小不同的数据表。我想删除包含 D 列中字符串“CHF”的行，但同样，D 列的大小每周都会发生变化。我什至不知道从哪里开始。我在写这个问题时看过推荐的类似问题，但仍然没有弄清楚。有什么解决办法吗？

标签： excelvba

Answer 1

删除行（对于...下一个循环壮举。联合）

• 调整常量部分中的值。

Option Explicit

Sub deleteRows()
    
    ' Define constants.
    Const wsName As String = "Sheet1"
    Const cFirst As Long = 2
    Const cCol As String = "D"
    Const Crit As String = "CHF"
    
    ' Define workbook, worksheet, and last row.
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim cLast As Long: cLast = ws.Cells(ws.Rows.Count, cCol).End(xlUp).Row
    
    ' Combine cells ('cCell') containing Criteria into Delete Range ('drg').
    Dim drg As Range
    Dim cCell As Range
    Dim i As Long
    For i = cFirst To cLast
        Set cCell = ws.Cells(i, cCol)
        If cCell.Value = Crit Then
            If drg Is Nothing Then
                Set drg = cCell
            Else
                Set drg = Union(drg, cCell)
            End If
        End If
    Next i
    
    ' Delete entire rows of Delete Range in one go.
    If Not drg Is Nothing Then
        drg.EntireRow.Delete
    End If
    
End Sub