VBA按名称批量删除非连续表格列的代码简化需求

完整简化代码 #

Sub dataSort()
    Dim tbl As ListObject
    ' 将数据区域转为表格并赋值给变量
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes)
    tbl.Name = "dataSort"
    
    ' 一行代码完成所有非连续列的删除
    For Each colName In Array("Vortex ID", "Lead Status", "Listing Status", "Address", "Mailing City", "Mailing State", "Mailing Zip", "List Price", "Lead Date", "Status Date", "Type", "Lot Size", "Phone Counter", "Email Counter", "Mail Counter", "House Number", "Tax ID"): tbl.ListColumns(colName).Delete: Next
End Sub

核心优化点 #

• 引入表格变量tbl，避免重复查找表格对象，提升执行效率
• 将需删除的列名整合为数组，通过For Each循环在一行内完成所有列删除（VBA中用冒号:分隔同一条语句中的多个操作）
• 后续如需调整删除的列，只需修改数组内的列名即可，维护更方便

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