Delete entire row based on criteria

**D_N_L** · 11-08-2011, 05:51 AM

Morning all,

I have attached an example worksheet. Sheets 1 and 2 show you how my data is now.

To explain, based on the name column ('B') I would like the code to delete the ENTIRE ROW when a person only features on either sheet 1 or 2 (but not both). For example, in the attached the person called 'Dave' would be deleted from Sheet 1 because he only features on Sheet 1 and not Sheet 2.

So if a person is present on both sheets then they are left alone. The spelling of each name will always be correct but the list of names may be in different orders (as they are in sheet 1 and 2 in the example).

Thanks

**nilem** · 11-08-2011, 06:09 AM

Try it. Now rows are highlighted. If everything is correct, delete this line

If Not rDel Is Nothing Then rDel.EntireRow.Interior.ColorIndex = 36

and uncomment this line

'        If Not rDel Is Nothing Then rDel.EntireRow.Delete

**Steffen Thomsen** · 11-08-2011, 06:33 AM

Hi,

Here's a bit shorter code


Sub Steffen()

For Each cell In Sheets(1).Range("B3:B" & Sheets(1).UsedRange.Rows.Count)
    If Sheets(2).Cells.Find(cell, , xlValues, xlPart) Is Nothing Then
        cell.EntireRow.Delete shift:=xlUp
    End If
Next cell

End Sub

Steffen Thomsen