Excel - Comparing cell A1 to entire A column in Sheet 2

November 2016


I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet.
I have experimented with many bits of macro code from others but so far no real success.

Figuring out how to say that in code is a bit difficult for me. I'm struggling to get the hang of this.

sub compare()
Dim LastRow_1 As Integer
Dim LastCol_1 As Integer
Dim Data_1 As range
Dim LastRow_2 As Integer
Dim LastCol_2 As Integer
Dim Data_2 As range

Dim Sh_1 As Worksheet
Dim Sh_2 As Worksheet

Dim X As Long
Dim Y As Long
Dim C_1 As range
Dim C_2 As range

Set Sh_1 = ActiveWorkbook.Sheets("Master")
Set Sh_2 = ActiveWorkbook.Sheets("Inventory")

LastRow_1 = Sh_1.range("A5000").End(xlUp).Row
LastCol_1 = Sh_1.range("A5000").End(xlToLeft).Column
Set Data_1 = Sh_1.range("A2").Resize(LastRow_1, LastCol_1)

LastRow_2 = Sh_2.range("A5000").End(xlUp).Row
LastCol_2 = Sh_2.range("A5000").End(xlToLeft).Column
Set Data_2 = Sh_2.range("A2").Resize(LastRow_2, LastCol_2)

For Each C_1 In Data_1
For Each C_2 In Data_2
If C_2 = C_1 Then
'found a cell on sheet2 that matched cell in sheet1
'now do what you need to do
Data_2.EntireRow.Copy Destination:=Worksheets("New_Master").range("A5000").End(xlUp).Offset(1, 0)

End If

Next C_2
Next C_1
end sub

When I run this it just copies everything to the sheet called New_Master, including the non-matches.

What am I missing? It has to be obvious, that's usually the case.


You just have to write:


You copy the whole range "Data_2", which corresponds to the column A of the sheet "Inventory"
If you want to copy only the rows in Data_2 that match with Data_1, do write


That's it!


Thanks to Ivan-hoe for this tip on the forum.

Related :

This document entitled « Excel - Comparing cell A1 to entire A column in Sheet 2 » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.