时间戳

首页 > 解决方案 > Excel VBA - 如果单元格包含 string1 或 string2 或 string3 等，则删除整行

excel - Excel VBA - 如果单元格包含 string1 或 string2 或 string3 等，则删除整行

问题描述

如果该行包含特定字符串，我想删除该行，但我需要为许多字符串多次执行此操作。

更具体地说，我的电子表格约为 3000 行和 30 列，但这每天都在变化。第一列，也是我唯一关心的，是网络上一个节点的名称，如果该节点以某个词开头或包含某个词（香蕉、苹果、橙子、菠萝），我需要删除整行并删除所有内容向上移动

这就是我到目前为止在@chrisneilsen 的帮助下所拥有的......

这正是我需要的，但是那里有我不需要的代码，或者可以清理吗？我已经混合了一些人的答案和建议，我想知道我是否创造了一个怪物......

Sub takeOutTheTrash()

    Dim rng As Range
    Dim ItemToDelete As Variant
    Dim ItemsToDelete As Variant
    ItemsToDelete = Array("Apple", "banana", "skittles", "grapes", "ORANGES")

    Set rng = ActiveSheet.UsedRange
    For Each ItemToDelete In ItemsToDelete
        deleteGarbage rng, ItemToDelete
    Next
    
End Sub

Sub deleteGarbage(rng As Range, Value As Variant)

    Dim pos As Long
    Dim rw As Long
    Dim col As Long

    For rw = rng.Rows.Count To 1 Step -1
        For col = 1 To rng.Columns.Count
            If InStr(LCase(rng.Cells(rw, col).Value2), LCase(Value)) > 0 Then
                rng.Rows(rw).EntireRow.Delete
                Exit For
            End If
        Next col
    Next rw
    
End Sub

标签： excelvba

解决方案

将 Delete Rows 例程抽象为单独的 Sub，并将您的值传递给它

就像是

Sub testIt()
    Dim rng As Range

    Set rng = ActiveSheet.UsedRange
    deleteGarbage rng, "Bananas"
    deleteGarbage rng, "SomethingElse"
End Sub

' To delete many items, create a variable that contains the items and iterate that.  
'  Eg a Range, an Array, or a Collection/Dictionary
Sub testItMany()
    Dim rng As Range
    Dim ItemsToDelete As Some Collection Object or Array
    Dim ItemToDelete As Variant

    Set rng = ActiveSheet.UsedRange
    For Each ItemToDelete In ItemsToDelete 
        deleteGarbage rng, ItemToDelete
    Next
End Sub



Sub deleteGarbage(rng as Range, Value as Variant)
    Dim pos As Long
    Dim rw As Long
    Dim col As long

    For rw = rng.Rows.Count To 1 Step -1
        For col = 1 to rng.Columns.Count
            If InStr(LCase(rng.Cells(rw, col).Value2), LCase(Value)) > 0 Then
                rng.Rows(rw).EntireRow.Delete
                Exit For
            End If
        Next col
    Next rw 
End Sub

如果这太慢，您可以进行优化以加快速度（例如使用变体数组）

推荐阅读