I've made a test file to duplicate this issue. When you are looping through a variable to delete columns based on the header I can't get the select case statement to register the final column unless I add a useless variable. Let me show you what I mean.
Sub test()
last_Column = Cells.Find(what:="*", After:=[A1], Searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
For x = last_Column To 1 Step -1
Header = Worksheets("Sheet1").Cells(1, x).Value
If Header = "" Then
GoTo next_x
Else
'continue
End If
Select Case Header
Case Cells(1, x).Value = "red", "blue", "green"
counter = counter + 1
Case Else
Worksheets("Sheet1").Cells(1, x).EntireColumn.Delete
End Select
next_x:
Next x
End Sub
Sub test2()
last_Column = Cells.Find(what:="*", After:=[A1], Searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
For x = last_Column To 1 Step -1
Header = Worksheets("Sheet1").Cells(1, x).Value
If Header = "" Then
GoTo next_x
Else
'continue
End If
Select Case Header
Case Cells(1, x).Value = "dumb", "red", "blue", "green"
counter = counter + 1
Case Else
Worksheets("Sheet1").Cells(1, x).EntireColumn.Delete
End Select
next_x:
Next x
End Sub
The only difference between test and test2 is test2 has an extra variable on my "Select Case" statement. Now if you take a normal worksheet and label A1 = "red", B1 = "blue", and C1 = "green" then the Sub test will delete "red". If you run test2 the code will not delete "red".
Maybe there is a solution for this and maybe not. I am more interested in the reasoning why test fails and test2 works. Does anyone know what is going on?
Bookmarks