VBA Select CASE not working in some column-CodePudding

I'm a beginner in VBA coding. I want to delete column for cells that contains the following in the range("A5:CC5") The code below doesn't work in certain column for some reason. For example column that contain "Mango" and "Grapes" is not deleted, others are working fine. If I run this code again, only then it will delete the "Mango" and "Grapes" column. Appreciate your advice on this coding below.

Sheets("ILP").Select
Set a = Range("A5:CC5")
For Each cell In a

Select Case cell.value
Case "Apple", _ 
    "Orange", _
    "Banana", _ 
    "Mango", _
    "Papaya", _
    "Grapes", _ 
    "Pineapple"
    cell.EntireColumn.Delete

End Select
Next
End Sub

CodePudding user response：

You should interact with the range of cells, from last to first, like this:

And to not worry about different spellings (upper/lower case), use the UCASE comparison. In this way, both 'banana' and 'Banana' will be taken into account. (the line with Debug.Print can be deleted. Put it only for inspection)

Option Explicit

Sub fnEliminateFruits()
    Dim rngItems As Excel.Range
    Dim intCell As Integer

    Set rngItems = Range("A5:CC5")
    For intCell = rngItems.Cells.Count To 1 Step -1
        Debug.Print "cell address: " & rngItems(intCell).Address & " range address: " & rngItems.Address
        Select Case UCase(rngItems(intCell).Value)
            Case "APPLE", _
                "ORANGE", _
                "BANANA", _
                "MANGO", _
                "PAPAYA", _
                "GRAPES", _
                "PINEAPPLE"
                rngItems(intCell).EntireColumn.Delete
        End Select
    Next
End Sub