Delete unmatched records

[lifeson]

I have 2 sheets:
Sheet 1 (Pack) contains a list of pack numbers (unique list)
Sheet 2 (PAckMat) that contains materals in the pack

I want to delete the records from sheet 2 that dont have a correspong record from sheet 1 based on the packID

I found this code which highlighted matched cells but I have tried to modify it to delete unmatched rows
Sub DeleteUnmatched()
Dim Head1 As Range, Head2 As Range
Dim Pack_List As Range, PackMat_List As Range
Dim c As Range, Hit As Range

Set Head1 = Sheets("Pack").Range("A1")
Set Head2 = Sheets("PackMat").Range("A1")

Set Pack_List = Range(Head1, Head1.End(xlDown))
Set PackMat_List = Range(Head2, Head2.End(xlDown))

For Each c In PackMat_List.Cells
Set Hit = Pack_List.Find(c.Value, , xlValues, xlWhole, , , False)
If Hit Is Nothing Then c.EntireRow.Delete
Next

End Sub

This doesn't quite work though as it leaves records and you have to run the macro a couple of times to work
Where have I gone wrong? :think:

[Bob Phillips]

When deleteing, ALWAYS work bottom up



Sub DeleteUnmatched()
Dim LastRow As Long
Dim i As Long
Dim wsPack As Worksheet

With Worksheets("Packmat")

Application.ScreenUpdating = False

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set wsPack = Worksheets("Pack")

For i = LastRow To 2 Step -1

If IsError(Application.Match(.Cells(i, "A").Value, wsPack.Columns(1), 0)) Then

.Rows(i).Delete
End If
Next i

Set wsPack = Nothing

Application.ScreenUpdating = True
End With
End Sub

[lifeson]

XLD
The code you supplied works fine on the demo I supplied but when I use the real data it doesnt work

Does it matter that the packID is not always numeric?

[Bob Phillips]

Seems to workl okay for me. What do you think that it is missing that it shouldn't?

[lifeson]

I have added some code to highlight the cells that should be kept

Open the sheet & click "highlight unmatched"

then run your code and you can see the list is different

EG pack 2215 has a linked material but does not exist on the pack sheet.

[Bob Phillips]

I have re-written as it was too slow, but I still get the same results. Seeing which should be retained isn't too helpful, if some get deleted. Tell me one that is deleted that shouldn't be.

[lifeson]

I have re-written as it was too slow, but I still get the same results. Seeing which should be retained isn't too helpful, if some get deleted. Tell me one that is deleted that shouldn't be.

I think we may be at crossed purposes here :bug: :bug:

When you click "Highlight Unmatched" this does not delete any records and just shows the records on sheet "PackMat", that have a matching record in column "A" PackID on sheet "Pack"
Therefore the rows that should be deleted are the ones that are not highlighted. This just shows the theory

When you click "Delete Unmatched XLD" I was expecting the rows that are not highlighted would be deleted.

e.g.
PackID 2235 exists on Sheet "Pack"
It also has links on sheet PackMat and these records should not be deleted from sheet PackMat

PackID 2215 does not exist on sheet "Pack"
Yet it has a record on sheet PackMat and this record should be deleted.

[lifeson]

:think: Still having problems getting this to work

Getting this which selects the range I want to keep, and highlights the cell
For Each c In PackMat_List.Cells
Set Hit = Pack_List.Find(c.Value, , xlValues, xlWhole, , , False)
If Not Hit Is Nothing Then c.Interior.ColorIndex = 3
Next

into something that deletes the rows that aren't selected