excel - 根据列表删除列

Question

我有一个宏，如果标题包含“组时间:”，则该宏会删除列

Sub remove_columns()
    For i = ActiveSheet.Columns.Count To 1 Step -1
        If InStr(1, Cells(1, i), "Group time:") Then Columns(i).EntireColumn.Delete
    Next i
End Sub

我还有其他列需要删除“总时间”、“最后一页”、“起始语言”...总共大约 10 个。

我可以创建上述宏的 10 个版本，但有没有一种方法可以让只有一个宏具有名称列表并删除这些列

谢谢

Answer 1

Sub remove_columns()

    Dim rngFound As Range
    Dim rngDel As Range
    Dim arrColumnNames() As Variant
    Dim varName As Variant
    Dim strFirst As String

    arrColumnNames = Array("Group time:", "Total time", "Last page", "Start language")

    For Each varName In arrColumnNames
        Set rngFound = Rows(1).Find(varName, Cells(1, Columns.Count), xlValues, xlPart)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Do
                If rngDel Is Nothing Then Set rngDel = rngFound Else Set rngDel = Union(rngDel, rngFound)
                Set rngFound = Rows(1).Find(varName, rngFound, xlValues, xlPart)
            Loop While rngFound.Address <> strFirst
        End If
    Next varName

    If Not rngDel Is Nothing Then rngDel.EntireColumn.Delete

    Set rngFound = Nothing
    Set rngDel = Nothing
    Erase arrColumnNames

End Sub