VBA Select CASE在某些列中不起作用

Question

我是VBA编码的初学者。我想删除包含以下单元格的列("A5:CC5"(由于某些原因，下面的代码在某些列中不起作用。例如，包含"；芒果"以及"；葡萄；未删除，其他人工作正常。如果我再次运行此代码，只有这样它才会删除"；芒果"以及"；葡萄；柱感谢您对以下编码的建议。

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

Answer 1

您应该与从最后到第一的单元格范围进行交互，如下所示：

为了不担心拼写不同(大写/小写(，请使用UCASE比较。这样，"香蕉"one_answers"香蕉"都会被考虑在内。(带有Debug.Print的行可以删除。只用于检查(

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