Compare to list, delete if not found

[bradjedis]

Greetings, Here is the situation:

I have a quite large file that I need to pare down. Sheet1 Col A holds the primary identifier. In Need of a macro that compares sheet1 Col A to Sheet2 Col A. If the Sheet1 Col A data is not in sheet2 Col A, then clear Cell, and move to next sheet1 Col A Cell..



Thanks,
Brad

[HansV]

Do you simply want to clear the cell, or should the cell or entire row be deleted, shifting up what's below?

[bradjedis]

Well, considering how much data hs to be slogged thru, I think the quickest would be to clear the cell and move on. I can sort and delete the rows manually.

[HansV]

Try this:

Code: Select all

Sub ClearMissing()
    Dim wsh1 As Worksheet
    Dim m1 As Long
    Dim a1 As Variant
    Dim r1 As Long
    Dim wsh2 As Worksheet
    Dim m2 As Long
    Dim a2 As Variant
    Set wsh1 = Worksheets("Sheet1")
    m1 = wsh1.Range("A" & wsh1.Rows.Count).End(xlUp).Row
    a1 = wsh1.Range("A1:A" & m1).Value
    Set wsh2 = Worksheets("Sheet2")
    m2 = wsh2.Range("A" & wsh2.Rows.Count).End(xlUp).Row
    a2 = wsh2.Range("A1:A" & m2).Value
    For r1 = 1 To m1
        If IsError(Application.Match(a1(r1, 1), a2, 0)) Then
            a1(r1, 1) = ""
        End If
    Next r1
    Application.ScreenUpdating = False
    wsh1.Range("A1:A" & m1).Value = a1
    Application.ScreenUpdating = True
End Sub

[bradjedis]

Yes indeed! That did it. many thanks!