VBA中Select Case删除列部分失效问题求助

你遇到的问题根源是正向遍历单元格时删除列，会导致后续列左移，循环跳过相邻的目标列。比如当代码删除包含"Mango"的列后，原本在它右侧的"Grapes"列会左移到被删除列的位置，但循环已经走到下一个单元格（原右侧第二列），这就导致"Grapes"列被跳过，第二次运行时它才会被检测到并删除。

修复方案1：从右往左遍历列 #

这种方法能避免删除列后位置偏移的问题，一次运行即可删完所有目标列：

Sub DeleteTargetColumns()
    Dim ws As Worksheet
    Dim lastCol As Integer
    Dim i As Integer
    
    Set ws = Sheets("ILP")
    lastCol = ws.Range("CC5").Column ' 获取范围的最后一列序号
    
    ' 从右往左遍历，删除列不会影响左侧待检查的列
    For i = lastCol To ws.Range("A5").Column Step -1
        Select Case ws.Cells(5, i).Value
            Case "Apple", "Orange", "Banana", "Mango", "Papaya", "Grapes", "Pineapple"
                ws.Columns(i).Delete
        End Select
    Next i
End Sub

修复方案2：批量删除（更高效） #

先收集所有需要删除的列，最后一次性删除，减少操作次数，适合列数较多的场景：

Sub DeleteTargetColumns_Batch()
    Dim ws As Worksheet
    Dim deleteRange As Range
    Dim cell As Range
    
    Set ws = Sheets("ILP")
    
    ' 遍历范围，收集所有要删除的列
    For Each cell In ws.Range("A5:CC5")
        Select Case cell.Value
            Case "Apple", "Orange", "Banana", "Mango", "Papaya", "Grapes", "Pineapple"
                If deleteRange Is Nothing Then
                    Set deleteRange = cell.EntireColumn
                Else
                    Set deleteRange = Union(deleteRange, cell.EntireColumn)
                End If
        End Select
    Next cell
    
    ' 批量删除目标列
    If Not deleteRange Is Nothing Then
        deleteRange.Delete
    End If
End Sub

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