VBA to remove entire row based on a specified list of values

[wenyusa]

So basically, I have a huge workbook. 187k+ rows. There are probably about 40k+ lines that could be removed from the sheet. However, they can only be removed based on a value in column M. This values determines whether it can either be kept or removed.

Currently there are 10 values in column M which should trigger the VBA to KEEP the row. If column M doesn't contain one of those values, then it can be deleted. If I have to hardcode the values that is ok for now, or I can put the values somewhere in the spreadsheet to reference. How do I write this? I'm struggling.

 

[NeonRedSharpie]

Sub deleteRows()

    For x = Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
        Select Case Cells(x, 13)
        
        Case "Value 1", "Value 2", "Value 3", "Value 4", _
                    "Value 5", "Value 6", "Value 7", _
                        "Value 8", "Value 9", "Value 10"
            'KEEP THE ROWS
        Case Else
            'NUKE THE ROWS
            Cells(x, 1).EntireRow.Delete
        End Select
    Next x
End Sub

This assumes strings. For numbers, remove the "".

 

[wenyusa]

It looks like this works perfect...

It is working from the bottom up?

How do you propose a way so I don't have to hard code the values? Is there a way that I can reference a range of cells on Sheet2?

 

[NeonRedSharpie]

It looks like this works perfect...

It is working from the bottom up?

How do you propose a way so I don't have to hard code the values? Is there a way that I can reference a range of cells on Sheet2?

Sub deleteRows()

    Dim list As Worksheet
    Dim data As Worksheet
    
    Set data = Sheets("Sheet1") 'CHANGE ME (This is where data will be deleted)
    Set list = Sheets("Sheet2") 'CHANGE ME (This is where the list of values to keep is in column A)
    


    With sheet2
        For x = list.Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
            Select Case list.Cells(x, 13)
            
            Case .Cells(1, 1), .Cells(2, 1), .Cells(3, 1), .Cells(4, 1), .Cells(5, 1), _
                    .Cells(6, 1), .Cells(7, 1), .Cells(8, 1), .Cells(9, 1), .Cells(10, 1)
                'KEEP THE ROWS
            Case Else
                'NUKE THE ROWS
                list.Cells(x, 1).EntireRow.Delete
            End Select
        Next x
    End With
End Sub

It is working from the bottom up because if you delete rows from the top down in a loop, it will skip rows.