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
Compare to list, delete if not found
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Compare to list, delete if not found
Do you simply want to clear the cell, or should the cell or entire row be deleted, shifting up what's below?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Compare to list, delete if not found
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.
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Compare to list, delete if not found
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 538
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Compare to list, delete if not found
Yes indeed! That did it. many thanks!