Delete row where meets criteria

[jasonhuibers]

I want to delete rows where the cell values in the range is equal to a list of numbers...

for example:

Column A
Donald
Brown
Smith
Jack
Doug

Delete rows where Column A contains Smith

 

[SkipVought]

hi,

values in the range is equal to a list of numbers...

Then you proceed with an example that bears absolutely no resemblence to that statement: you have TEXT in the column AND you are finding only ONE VALUE??????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jasonhuibers]

Ooops - sorry about that!

Lets try this again:
I want to delete rows where the cell values in the range is equal to a list of values...

For example:


Column A
Donald
Brown
Smith
Jack
Doug

Delete rows where column A contains Smith and Brown so the end result would be:

Column A
Donald
Jack
Doug

 

[SkipVought]

What code do you have so far and where are you stuck?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jasonhuibers]

Obviously Skippy I do not have any code as the reason for making this post...

 

[SkipVought]

No, it is NOT obvious that you would not have ANY code.

The simplest way is to use MS Query faq68-5829
[tt]
1) have your source data data on a sheet with unique headings. faq68-5184

2) have your list on a separate sheet, with a heading.

3) SQL assuming that your source data in in Sheets and your list is in Sheet2, with then source data names heading Name and the list heading Nam

SELECT *
FROM `Sheet1$` 

Where Name not in (Select Nam from `Sheet2$`)

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[stduc]

Alternatively - if you are not familiar with ms Query and / or SQL then here is some simple code that will do the job. It assumes your data is in sheet1 and a table of values where data to be matched to column A is in sheet2. It should give you something to work on.

Sub DeleteMatchedRows()

    Sheets("sheet1").Select
    ToDeleteRow = 1
    
    Do Until Sheets("Sheet2").Cells(ToDeleteRow, 1).Value = ""
    
        RowToDelete = 1
    
        Do Until Sheets("Sheet1").Cells(RowToDelete, 1).Value = ""
    
            If Sheets("Sheet1").Cells(RowToDelete, 1).Value = Sheets("Sheet2").Cells(ToDeleteRow, 1).Value Then
            
                Rows(RowToDelete & ":" & RowToDelete).Select
                Application.CutCopyMode = False
                Selection.Delete Shift:=xlUp
                RowToDelete = RowToDelete - 1
               
            End If
            
            RowToDelete = RowToDelete + 1
        
        Loop
        
        ToDeleteRow = ToDeleteRow + 1
        
    Loop

End Sub

 

[jasonhuibers]

Thank you both!