I had a situation today where I had a spreadsheet that contained hundreds of columns. I only needed five or so of these and I didn’t fancy going through them one-by-one to delete the unnecessary ones. I found the below snipped of VBA on stackoverflow. The code ran almost instantaneously and deleted all columns that I didn’t need. The country names are the columns that I needed to keep. I didn’t change/need the part of the code that deletes cells if they don’t contain the string ‘homer’.
Sub deleteIrrelevantColumns() Dim currentColumn As Integer Dim columnHeading As String ActiveSheet.Columns("L").Delete For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1 columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value 'CHECK WHETHER TO KEEP THE COLUMN Select Case columnHeading Case "England", "New Zealand", "India", "South Africa", "Malaysia", "China", "Philippines", "Scotland" 'Do nothing Case Else 'Delete if the cell doesn't contain "Homer" If Instr(1, _ ActiveSheet.UsedRange.Cells(1, currentColumn).Value, _ "Homer",vbBinaryCompare) = 0 Then ActiveSheet.Columns(currentColumn).Delete End If End Select Next End Sub