A few words of thanks would be greatly appreciated.

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


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.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Comparing cell A1 to entire A column in Sheet 2 », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!