How to delete multiple columns by title?-CodePudding

I have a worksheet that has about 80 columns, I want to remove a bunch of them and these are updated on an ongoing basis and may move around so I want to delete them by their column title. However, when I run this macro, it works, but I actually need to run it at least 4-5 times for it to completely remove all the correct columns. What is going on here? Is there a correct and more elegant way to do this?

Sub Modify_Table()
    ' Remove specific columns
    Set MR = ActiveSheet.Range("A1:BQ1")
  
    For Each cell In MR
        If cell.Value = "Attack ID" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Last Change (UTC)" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Tools/Malware" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Labels" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Attacker Profile" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Leak Rate" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Footprint" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Target Node" Then
            cell.EntireColumn.Delete
        ElseIf cell.Value = "Test Time (UTC)" Then
            cell.EntireColumn.Delete
      ...
        Else
    End If
    Next
  
End Sub

CodePudding user response：

Iterate from the last item to the first item when deleting items. Otherwise, you will end up skipping items. This is because the items are shifted when you delete one

In this case, I would create a range that contains all the headers to be deleted and then delete them all at once.

Sub Modify_Table()
    ' Remove specific columns
    Dim HeaderRange As Range
    
    Set HeaderRange = ActiveSheet.Range("A1:BQ1")
  
    Dim Target As Range
    Dim Cell As Range
    Dim Titles As Variant
    Titles = Array("Attack ID", "Last Change (UTC)", "Tools/Malware", "Labels", "Attacker Profile", "Leak Rate", "Footprint", "Target Node", "Test Time (UTC)")
    
    For Each Cell In HeaderRange
         If Not IsError(Application.Match(Cell.Value, Titles, 0)) Then
            If Target Is Nothing Then
                Set Target = Cell
            Else
                Set Target = Union(Target, Cell)
            End If
         End If
    Next
    
    If Not Target Is Nothing Then Target.EntireColumn.Delete

End Sub