如何通过VBA按列标题批量选择并删除指定列？

需要删除Excel下载文件中的指定列，因列数可能变化，不能用固定单元格引用，改用列标题定位。修改原单列查找脚本后出现编译错误：Compile Error: Wrong number of arguments or invalid property assignment。

原单列查找脚本 #

Sub FindAddressColumn()
'Updateby Extendoffcie
    Dim xRg As Range
    Dim xRgUni As Range
    Dim xFirstAddress As String
    Dim xStr As String
    On Error Resume Next
    xStr = "Name"
    Set xRg = Range("A1:P1").Find(xStr, , xlValues, xlWhole, , , True)
    If Not xRg Is Nothing Then
        xFirstAddress = xRg.Address
        Do
            Set xRg = Range("A1:P1").FindNext(xRg)
            If xRgUni Is Nothing Then
                Set xRgUni = xRg
            Else
                Set xRgUni = Application.Union(xRgUni, xRg)
            End If
        Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)
    End If
    xRgUni.EntireColumn.Select
End Sub

报错的修改后脚本 #

Sub deleteEmptyCols()
'Updateby Extendoffcie
    Dim xRg As Range
    Dim xRgUni As Range
    Dim xFirstAddress As String
    Dim xStr As String
    On Error Resume Next
    xStr = Range("PREVIOUS TRANSACTION ID", "PARENT TRANSACTION ID", "TRANSACTION COMMENTS", "HP INVOICE NUMBER", "REPORTER PURCHASE ORDER ID", "PARTNER PURCHASE PRICE", "CUSTOMER TO CHANNEL PARTNER PURCHASE ORDER ID", "PARTNER INTERNAL TRANSACTION ID", "PARTNER REQUESTED REBATE AMOUNT", "PARTNER COMMENT", "DEAL/PROMO ID #2", "DEAL/PROMO ID #3", "DEAL/PROMO ID #4", "DEAL/PROMO ID #5", "DEAL/PROMO ID #6", "DEAL BUNDLE ID #1", "REBATE DEAL 1 MINIMUM RESELLER QUANTITY", "REBATE DEAL 1 MAX RESELLER QUANTITY", "EXTENDED REFERENCE PRICE (SNOP) 1", "DEAL BUNDLE ID #2" & _
    "REBATE DEAL 2", "REBATE DEAL 2 START DATE", "REBATE DEAL 2 END DATE", "REBATE DEAL 2 MC CODE", "REBATE DEAL 2 MINIMUM RESELLER QUANTITY", "REBATE DEAL 2 MAX RESELLER QUANTITY", "REBATE DEAL 2 DEAL VERSION", "REBATE DEAL 2 REMAINING QTY", "BACKEND DEAL DISCOUNT TYPE BASE 2", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 2", "BACKEND DEAL NET TOTAL 2", "DCT FLAG DEAL 2", "EXTENDED REFERENCE PRICE (SNOP) 2", "DEAL BUNDLE ID #3", "REBATE DEAL 3", "REBATE DEAL 3 START DATE", "REBATE DEAL 3 END DATE", "REBATE DEAL 3 MC CODE", "REBATE DEAL 3 MINIMUM RESELLER QUANTITY", "REBATE DEAL 3 MAX RESELLER QUANTITY" & _
"REBATE DEAL 3 DEAL VERSION", "REBATE DEAL 3 REMAINING QTY", "BACKEND DEAL DISCOUNT TYPE BASE 3", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 3", "BACKEND DEAL NET TOTAL 3", "DCT FLAG DEAL 3", "EXTENDED REFERENCE PRICE (SNOP) 3", "DEAL BUNDLE ID #4", "REBATE DEAL 4", "REBATE DEAL 4 START DATE", "REBATE DEAL 4 END DATE", "REBATE DEAL 4 MC CODE", "REBATE DEAL 4 MINIMUM RESELLER QUANTITY", "REBATE DEAL 4 MAX RESELLER QUANTITY", "REBATE DEAL 4 DEAL VERSION", "REBATE DEAL 4 REMAINING QTY", "BACKEND DEAL DISCOUNT TYPE BASE 4", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 4", "EXTENDED REFERENCE PRICE (SNOP) 4", "BACKEND DEAL NET TOTAL 4" & _
"DCT FLAG DEAL 4", "DEAL BUNDLE ID #5", "REBATE DEAL 5", "REBATE DEAL 5 START DATE", "REBATE DEAL 5 END DATE", "REBATE DEAL 5 MC CODE", "REBATE DEAL 5 MINIMUM RESELLER QUANTITY", "REBATE DEAL 5 MAX RESELLER QUANTITY", "REBATE DEAL 5 DEAL VERSION", "REBATE DEAL 5 REMAINING QTY", "BACKEND DEAL DISCOUNT TYPE BASE 5", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 5", "BACKEND DEAL NET TOTAL 5", "DCT FLAG DEAL 5", "EXTENDED REFERENCE PRICE (SNOP) 5", "DEAL BUNDLE ID #6", "PARTNER REPORTED CBN#", "PARTNER REFERENCE", "INTERCOMPANY FLAG", "SOLD TO STATE" & _
"END USER CUSTOMER NAME", "END USER ID", "UPFRONT DEAL ID", "SUB REGION PARTNER LOCATOR NUMBER", "WW PARTNER LOCATOR NUMBER", "CUSTOMER ID", "EXTENDED SHIPMENT PRICE", "DERIVED INVOICE PRICE", "REBATE ADJUSTMENT", "ELIGIBLE SALES ADJUSTMENT", "IS MAXCAP MET", "BUNDLE COMPLETENESS STATUS", "IS MINCAP MET", "CREDIT MEMO DATE", "CREDIT MEMO REFERENCE", "PAID QUANTITY", "PAID AMOUNT", "PAID COMMENTS", "DNQ", "STACKING VALIDATION" & _
"ADJUSTMENT COMMENTS", "REVERSAL PAYMENT REFERENCE", "FCM PRICING REBATE FLAG", "CASE NUMBER", "CASE STATUS", "CASE LAST STATUS UPDATE DATE", "CASE CREATION DATE", "CASE COMMENT", "REASON CODE", "REASON DESCRIPTION", "PRICE POINT WARNING DETAILS")

    Set xRg = Range("A1:GO1").Find(xStr, , xlValues, xlWhole, , , True)
    If Not xRg Is Nothing Then
        xFirstAddress = xRg.Address
        Do
            Set xRg = Range("A1:P1").FindNext(xRg)
            If xRgUni Is Nothing Then
                Set xRgUni = xRg
            Else
                Set xRgUni = Application.Union(xRgUni, xRg)
            End If
        Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)
    End If
    xRgUni.EntireColumn.Select
End Sub

错误原因 #

1. 变量赋值错误：原脚本中xStr是单个字符串，修改后错误用Range()存储多个列标题，Range()仅接受单元格引用而非字符串列表，导致参数数量不符。
2. 字符串拼接错误：用& _时未正确分隔标题，反而将多个标题拼接为长字符串，无法被Find方法识别。
3. 查找范围不一致：初始查找范围是A1:GO1，但FindNext用了A1:P1，会遗漏超出P列的标题。

修正后的代码 #

Sub DeleteSpecifiedColumnsByHeader()
    Dim headerList As Variant
    Dim header As Variant
    Dim targetRng As Range
    Dim foundRng As Range
    Dim firstAddress As String
    
    ' 定义需要删除的列标题列表
    headerList = Array( _
        "PREVIOUS TRANSACTION ID", "PARENT TRANSACTION ID", "TRANSACTION COMMENTS", _
        "HP INVOICE NUMBER", "REPORTER PURCHASE ORDER ID", "PARTNER PURCHASE PRICE", _
        "CUSTOMER TO CHANNEL PARTNER PURCHASE ORDER ID", "PARTNER INTERNAL TRANSACTION ID", _
        "PARTNER REQUESTED REBATE AMOUNT", "PARTNER COMMENT", "DEAL/PROMO ID #2", _
        "DEAL/PROMO ID #3", "DEAL/PROMO ID #4", "DEAL/PROMO ID #5", "DEAL/PROMO ID #6", _
        "DEAL BUNDLE ID #1", "REBATE DEAL 1 MINIMUM RESELLER QUANTITY", _
        "REBATE DEAL 1 MAX RESELLER QUANTITY", "EXTENDED REFERENCE PRICE (SNOP) 1", _
        "DEAL BUNDLE ID #2", "REBATE DEAL 2", "REBATE DEAL 2 START DATE", _
        "REBATE DEAL 2 END DATE", "REBATE DEAL 2 MC CODE", _
        "REBATE DEAL 2 MINIMUM RESELLER QUANTITY", "REBATE DEAL 2 MAX RESELLER QUANTITY", _
        "REBATE DEAL 2 DEAL VERSION", "REBATE DEAL 2 REMAINING QTY", _
        "BACKEND DEAL DISCOUNT TYPE BASE 2", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 2", _
        "BACKEND DEAL NET TOTAL 2", "DCT FLAG DEAL 2", "EXTENDED REFERENCE PRICE (SNOP) 2", _
        "DEAL BUNDLE ID #3", "REBATE DEAL 3", "REBATE DEAL 3 START DATE", _
        "REBATE DEAL 3 END DATE", "REBATE DEAL 3 MC CODE", _
        "REBATE DEAL 3 MINIMUM RESELLER QUANTITY", "REBATE DEAL 3 MAX RESELLER QUANTITY", _
        "REBATE DEAL 3 DEAL VERSION", "REBATE DEAL 3 REMAINING QTY", _
        "BACKEND DEAL DISCOUNT TYPE BASE 3", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 3", _
        "BACKEND DEAL NET TOTAL 3", "DCT FLAG DEAL 3", "EXTENDED REFERENCE PRICE (SNOP) 3", _
        "DEAL BUNDLE ID #4", "REBATE DEAL 4", "REBATE DEAL 4 START DATE", _
        "REBATE DEAL 4 END DATE", "REBATE DEAL 4 MC CODE", _
        "REBATE DEAL 4 MINIMUM RESELLER QUANTITY", "REBATE DEAL 4 MAX RESELLER QUANTITY", _
        "REBATE DEAL 4 DEAL VERSION", "REBATE DEAL 4 REMAINING QTY", _
        "BACKEND DEAL DISCOUNT TYPE BASE 4", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 4", _
        "EXTENDED REFERENCE PRICE (SNOP) 4", "BACKEND DEAL NET TOTAL 4", "DCT FLAG DEAL 4", _
        "DEAL BUNDLE ID #5", "REBATE DEAL 5", "REBATE DEAL 5 START DATE", _
        "REBATE DEAL 5 END DATE", "REBATE DEAL 5 MC CODE", _
        "REBATE DEAL 5 MINIMUM RESELLER QUANTITY", "REBATE DEAL 5 MAX RESELLER QUANTITY", _
        "REBATE DEAL 5 DEAL VERSION", "REBATE DEAL 5 REMAINING QTY", _
        "BACKEND DEAL DISCOUNT TYPE BASE 5", "BACKEND DEAL REBATE AMOUNT PER UNIT TOTAL 5", _
        "BACKEND DEAL NET TOTAL 5", "DCT FLAG DEAL 5", "EXTENDED REFERENCE PRICE (SNOP) 5", _
        "DEAL BUNDLE ID #6", "PARTNER REPORTED CBN#", "PARTNER REFERENCE", _
        "INTERCOMPANY FLAG", "SOLD TO STATE", "END USER CUSTOMER NAME", _
        "END USER ID", "UPFRONT DEAL ID", "SUB REGION PARTNER LOCATOR NUMBER", _
        "WW PARTNER LOCATOR NUMBER", "CUSTOMER ID", "EXTENDED SHIPMENT PRICE", _
        "DERIVED INVOICE PRICE", "REBATE ADJUSTMENT", "ELIGIBLE SALES ADJUSTMENT", _
        "IS MAXCAP MET", "BUNDLE COMPLETENESS STATUS", "IS MINCAP MET", _
        "CREDIT MEMO DATE", "CREDIT MEMO REFERENCE", "PAID QUANTITY", "PAID AMOUNT", _
        "PAID COMMENTS", "DNQ", "STACKING VALIDATION", "ADJUSTMENT COMMENTS", _
        "REVERSAL PAYMENT REFERENCE", "FCM PRICING REBATE FLAG", "CASE NUMBER", _
        "CASE STATUS", "CASE LAST STATUS UPDATE DATE", "CASE CREATION DATE", _
        "CASE COMMENT", "REASON CODE", "REASON DESCRIPTION", "PRICE POINT WARNING DETAILS" _
    )
    
    ' 动态获取表头行的所有非空单元格，适配列数变化
    Set targetRng = ActiveSheet.Rows(1).SpecialCells(xlCellTypeConstants)
    
    ' 遍历每个标题，查找对应的列
    For Each header In headerList
        Set foundRng = targetRng.Find(What:=header, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not foundRng Is Nothing Then
            firstAddress = foundRng.Address
            ' 处理重复标题
            Do
                ' 合并目标列
                If targetRng Is Nothing Then
                    Set targetRng = foundRng.EntireColumn
                Else
                    Set targetRng = Union(targetRng, foundRng.EntireColumn)
                End If
                Set foundRng = targetRng.FindNext(foundRng)
            Loop While Not foundRng Is Nothing And foundRng.Address <> firstAddress
        End If
    Next header
    
    ' 确认有目标列后执行删除
    If Not targetRng Is Nothing Then
        targetRng.Delete Shift:=xlToLeft
    End If
End Sub

代码说明 #

1. 用数组存储标题列表：通过Array()正确定义所有需删除的列标题，避免参数错误。
2. 动态获取表头范围：用SpecialCells(xlCellTypeConstants)获取表头行所有非空单元格，无需固定列范围，自动适配列数变化。
3. 遍历查找每个标题：逐个查找数组中的标题，处理重复标题并合并所有目标列。
4. 安全删除列：确认找到目标列后再执行删除，避免空引用错误。

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