基于Excel工作表A列和H列的值删除行的VBA技术问询

以下是适配你需求的VBA代码，通过字典存储A列值对应的H列禁用列表，实现灵活的行删除逻辑：

Sub DeleteRowsByConditions()
    Dim lastRow As Long
    Dim rowNum As Long
    Dim conditionDict As Object
    Dim colAValue As String
    Dim colHValue As String
    
    ' 创建字典存储A列值对应的H列禁用列表
    Set conditionDict = CreateObject("Scripting.Dictionary")
    ' 添加匹配规则：键为A列值，值为对应的H列禁用值数组
    conditionDict("A") = Array("Z", "Y", "X")
    conditionDict("B") = Array("X", "W", "V")
    ' 可继续添加更多规则，比如 conditionDict("C") = Array("U", "T", "S")
    
    ' 获取A列最后一行行号
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 从最后一行往上循环，避免删除行导致索引错乱
    For rowNum = lastRow To 1 Step -1
        colAValue = Trim(Range("A" & rowNum).Value)
        colHValue = Trim(Range("H" & rowNum).Value)
        
        ' 检查当前A列值是否在规则字典中
        If conditionDict.Exists(colAValue) Then
            ' 检查H列值是否在对应的禁用列表里
            If IsInArray(colHValue, conditionDict(colAValue)) Then
                Rows(rowNum).Delete
            End If
        End If
    Next rowNum
    
    Set conditionDict = Nothing
End Sub

' 辅助函数：检查值是否在数组中
Function IsInArray(searchValue As String, arr As Variant) As Boolean
    Dim element As Variant
    For Each element In arr
        If element = searchValue Then
            IsInArray = True
            Exit Function
        End If
    Next element
    IsInArray = False
End Function

关键说明： #

• 字典存储规则：通过Scripting.Dictionary将A列值和对应的H列禁用列表关联，后续新增匹配规则只需在字典中添加键值对，无需修改核心循环逻辑。
• 反向循环：从最后一行向第一行遍历，避免删除行后导致未遍历的行索引偏移，确保所有行都能被正确检查。
• 辅助函数IsInArray：封装值与数组的匹配逻辑，让主代码更简洁易读。
• 后期绑定字典：使用CreateObject("Scripting.Dictionary")无需手动添加引用，兼容性更好。如果需要早期绑定，可在VBA编辑器中通过「工具」→「引用」勾选Microsoft Scripting Runtime，然后将Dim conditionDict As Object改为Dim conditionDict As Dictionary。

内容的提问来源于stack exchange，提问作者Mushroom495