Compare to list, delete if not found

bradjedis
4StarLounger
Posts: 436
Joined: 30 Mar 2010, 18:49
Location: United States

Compare to list, delete if not found

Post by 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

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Compare to list, delete if not found

Post by HansV »

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

bradjedis
4StarLounger
Posts: 436
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Compare to list, delete if not found

Post by 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.

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Compare to list, delete if not found

Post by 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
Regards,
Hans

bradjedis
4StarLounger
Posts: 436
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Compare to list, delete if not found

Post by bradjedis »

Yes indeed! That did it. many thanks!