Macro to compare values in array against cell value

[wester69]

Hello,

Needing help passing the values in the array to a variable? Or is there a better method?

I am not even sure even if I using the right method? I have large list and want to delete all the rows that are not needed based on a value in one column. I thought a static array would work but needing some help.

Basically I want to delete all rows that have Orange, Yellow & Red and keep the ones that have Green, Blue and Purple in column B.

I thought my "For Each i" would grab the values but it appears it is only a counter. Can't figure out how to pass the values of the array to compare against the current cell value?


Sub Filter_Values()

'Creating array arrList
Dim arrList(2)
arrList (0) = Orange
arrList (1) = Yellow
arrList (2) = Red

'Count total rows and query column B against array values
Dim lastrow As Long
Dim r As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

For r = lastrow To 1 Step -1
For Each i In arrList
If UCase(Cells(r, 2).Value) = i Then Rows(r).Delete
Next
Next r

End Sub


Any help would be apprecieated!

-Wesley