excel - 如果在各种列中找不到部分字符串，则删除行：VBA

Question

我正在尝试创建一个宏，该宏将删除该行中任何单元格中不包含部分字符串“.csv”的行。需要注意的是，该字符串不会出现在同一列中。

如果在 B 列中找到“.csv”显式，则以下代码有效：（我需要部分字符串搜索和跨多列）

    Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long


' Defines LastRow as the last row of data based on column E
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).row


' Sets check range as E1 to the last row of E
Set cRange = Range("B1:B" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
        If .Value <> ".csv" Then
        'And .Value <> "PENDING" And .Value <> "CANCELLED" Add to line above if you want to add specifications'
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x


End Sub

标签： excelvbamultiple-columnsdelete-row

Answer 1

已发布代码的此改编COUNTIF用于搜索整行以查找与 的任何部分匹配.csv，如果找不到，则删除整行。

如果需要，它可以调整为仅删除行的一部分，但需要更多信息。

Option Explicit

Sub DeleteRows()
' Define variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long
Dim Res As Variant

    ' Define LastRow as the last row of data based on column E
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

    ' Sets check range as E1 to the last row of E
    Set cRange = Range("B1:B" & LastRow)

    ' For each cell in the check range, working from the bottom upwards
    For x = cRange.Cells.Count To 1 Step -1
        ' find if '.csv' found in any cell in the row
        Res = Evaluate("COUNTIF(" & x & ":" & x & ", ""*.csv*"")")
        
        ' if '.csv' not found delete row
        If Res = 0 Then
            ActiveSheet.Rows(x).Delete
        End If
        
    Next x

End Sub