If cell does not contain values from a named list-CodePudding

I have a piece of VBA code that sorts through a worksheet and deletes all rows that in which one of the columns does not contain specific values

Sub DeleteRows()
' Defines variables
Dim Cell As Range, cRange As Range, LastRow As Long, x As Long, TestRange As Range, MyRange As Range


' Defines LastRow as the last row of data based on column C
LastRow = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row


' Sets check range as E1 to the last row of C
Set cRange = Range("C1:C" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If the cell does not contain one of the listed values then...
        If .Value <> "Location1" And .Value <> "Location2" And .Value <> "Location3" Then
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell, working upwards
Next x

End Sub

The problem is I have a very long and growing list of locations. Instead of specifying locations (e.g., "Location1", "Location2", etc.), I want the code to compare each cell in the check range against a named list ("ReferenceLocations") and delete the row if the cell contains a location name not in that list.

How can I change that section of code (if .value<>...) to achieve this?

CodePudding user response：

Using Application.Match and IsError:

If IsError(Application.Match(.Value, Range("ReferenceLocations"), 0)) Then
   .EntireRow.Delete
End If

This assumes that your named range is a single row or column. If that is not a safe assumption, then:

If Application.CountIfs(Range("ReferenceLocations"), .Value) = 0
   .EntireRow.Delete
End If