lifeson
09-27-2007, 02:23 AM
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:
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: