Use VBA to check if cell value is in a named range and delete row if not

[julia55]

Ok, so I can't figure out how to do this at all. I basically have a spreadsheet with varrying number of rows. I want to check the value of a specific column (K) and if the value of that column is not in my named range I want it to delete the entire row. Currently I am using a lot of macros like the one below and calling them all in another macro, but that is becoming to cumbersome. I am a noob at VBA so please provide me with some detailed help if possible. Thanks so much!

Sub DeleteACCUTRAC()
    
If ActiveSheet.Name = "Tracking System Compliance" Then
Range("J:J").Select

Else: Range("K:K").Select
End If

    Dim c As Range
    Dim SrchRng
    Set SrchRng = Selection
    Do
        Set c = SrchRng.Find("ACCUT", LookIn:=xlValues, LookAt _
        :=xlPart)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
End Sub

 

[JLGWhiz]

Try this on a copy of your file before applying to the original. Deletions by code cannot be recovered with Undo.

Code:
Sub DeleteACCUTRAC()
Dim sh As Worksheet, lr As Long, rng As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, "K").End(xlUp).Row
Set rng = sh.Range("K2:K" & lr)
If sh.Name <> "Tracking System Compliance" Then
For i = lr To 2 Step -1
If sh.Cells(i, "K") <> "ACCUT" Then
sh.Cells(i, "K").EntireRow.Delete
End If
Next
End If
End Sub
Code:

 

[julia55]

How can I change ACCUT to look at my named range instead and search for the value within that named range?

 

[JLGWhiz]

How can I change ACCUT to look at my named range instead and search for the value within that named range?

You want to check each item in your named range against each item in column K?

 

[julia55]

Yes, I want the macro to look at the value in column K, if the value in column K is not in the named range I want to delete the entire row.

 

[JackDanIce]

Try:

Sub Delete_ACCUT()
    

Dim i As Long
Dim rng As Range
    

Application.ScreenUpdating = False

    
With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("K1").Select
    If .Name = "Tracking System Compliance" Then .Range("J1").Select
    i = Cells(Rows.Count, Selection.Column).End(xlUp).Row
    Set rng = .Range(.Cells(1, Selection.Column), .Cells(i, Selection.Column))
    rng.AutoFilter field:=1, Criteria1:="ACCUT"
    Set rng = rng.Columns("A").SpecialCells(xlCellTypeVisible)
    If Not rng Is Nothing Then rng.EntireRow.Delete

End With

Application.ScreenUpdating = True
Set rng = Nothing

End Sub

 

[JLGWhiz]

Yes, I want the macro to look at the value in column K, if the value in column K is not in the named range I want to delete the entire row.

I am trying to understand the named range make up. Is it one cell or a range of cells. It would also help to know the name of the range.

 

[JLGWhiz]

Something like this would check each value in column K against the named range values and if not found in the named range, delete the row for the value in column K.

Generic code:
dim nRng as Range, rng As Range
Set nRng = Range("Named Range") 'Substitute actual name of range
Set rng = Range("K2", ActiveSheet.Cells(Rows.Count, "K").End(xlUP)) 'get col K parameters
For Each c In rng 'Look at each item in col K
If WorksheetFunction.CountIf(nRng, c.Value) = 0 Then 'Evaluate against named range
c.EntireRow.Delete 'Delete rows with no match to named range
End If
Next
Generic code: