Excel VBA问题：使用If语句与For循环无法批量删除列

Hey there! I see you're struggling to delete multiple columns in Sheet1 based on specific headers using VBA, especially since your initial For/If loop approach isn't working. Let's sort this out—here's a robust solution that avoids the common pitfalls of deleting columns while looping.

Why Your Initial Loop Failed #

The main gotcha with looping left-to-right when deleting columns is that once you remove a column, all columns to the right shift left by one. This means your loop will skip over columns because their positions change mid-execution. The simple fix? Loop from right to left instead.

Working VBA Code #

Here's a revised version of your macro that handles bulk column deletion reliably:

Sub SPO_EditDocument_ButtonClick()
    Dim ws As Worksheet
    Dim deleteHeaders As Variant
    Dim headerRow As Integer
    Dim lastCol As Integer
    Dim i As Integer
    Dim matchResult As Variant
    
    ' Target the sheet we need to edit
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' List of headers to delete (easy to update later)
    deleteHeaders = Array("First Name", "Surname", "DoB", "Gender", "Year")
    
    ' Assume headers are in row 1—adjust this number if your headers are elsewhere
    headerRow = 1
    
    ' Find the last used column in the header row
    lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
    
    ' Loop from the LAST column to the FIRST to avoid skipping columns post-deletion
    For i = lastCol To 1 Step -1
        ' Check if the current header is in our delete list
        matchResult = Application.Match(ws.Cells(headerRow, i).Value, deleteHeaders, 0)
        
        ' If a match is found, delete the entire column
        If Not IsError(matchResult) Then
            ws.Columns(i).Delete
        End If
    Next i
    
    ' Optional: Confirm completion to the user
    MsgBox "Specified columns deleted successfully!", vbInformation
End Sub

Key Details in This Code #

• Explicit Worksheet Reference: We use Set ws = ThisWorkbook.Sheets("Sheet1") to make sure we're editing the correct sheet, no accidental changes elsewhere.
• Editable Header Array: Storing headers in an array lets you add/remove items in seconds without rewriting messy conditional logic.
• Right-to-Left Loop: By starting at the last column and moving left (Step -1), we eliminate the column-shift skipping issue entirely.
• Clean Match Check: The Application.Match function replaces a long chain of If...Or checks, making the code cleaner and easier to maintain.

How to Link to Your Execute Button #

1. Right-click the Execute button on your Execution worksheet.
2. Select Assign Macro from the menu.
3. Choose SPO_EditDocument_ButtonClick from the list and click OK.

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