My sheet has about 300 columns of data and I want to delete all columns that don't contain specific names in Row 2, i.e., TEST1, TEST2, TEST3, TEST4. This is not formated as a table.
The code below "works" but seems to go into an infinite loop based on Debug.Print i. Without, i = i - 1, the code will complete without fully deleting columns, so I have to re-run multiple times.
Sub cleanCl()
Dim rng As Range
Dim ccls as Integer
Application.ScreenUpdating = False
ccls = ActiveSheet.UsedRange.Columns.Count
For i = 1 To ccls
' ccls = ActiveSheet.UsedRange.Columns.Count ' decrease loop count
Set rng = Range(Cells(2, i).Address)
If Not (rng Like "*TEST1" Or rng Like "*TEST2" Or rng Like "*TEST3" Or rng Like "*TEST4") Then
rng.EntireColumn.Delete
i = i - 1
End If
Debug.Print i
Next
Application.ScreenUpdating = True
End Sub
Bookmarks