Comparing columns across workbooks !

svkroy · Nov 15, 2011

Hi,

I have two workbooks. 1.xlsx & 2.xlsx.

I would like to match the cell values of column A (has headers) of 1.xlsx with that of Coulumn A (has headers) of 2.xlsx (there would be duplicates in both).

If they don't match then i would like to delete the row pertaining to that cell in 1.xlsx.

Till now, after some clues, I figured out this (but unfortunately its deleting everything).

Sub no2()

Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim Rng As Range
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim Rng2 As Range
Dim Cell As Range
Dim foundcell As Range

Workbooks.Open ("D:\1.xlsx")
Workbooks.Open ("D:\2.xlsx")

Set wbSource = Workbooks("2.xlsx")
Set wsSource = wbSource.Worksheets("Sheet1")
With wsSource
Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

Set wbMaster = Workbooks("1.xlsx")
Set wsMaster = wbMaster.Worksheets("Sheet1")
With wsMaster
Set Rng2 = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

For Each Cell In Rng

Set foundcell = Rng2.Find(what:=Cell.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)

If Not foundcell Is Nothing Then
Rownum = foundcell.Row
Else
Rng.EntireRow.Delete
End If

Next Cell

End Sub

Any help is much appreciated.

xenou · Nov 15, 2011

I don't know if it matters but deleting rows in a range can sometimes cause issues when you are simultaneously looping the rows (being that parts of the range are disappearing as you go). You could try instead, if Rng is only one column:

Code:

Dim i As Long

For i = Rng.Cells.Count to 1 Step -1 '//Assumes Rng is One Column Only

	Set foundcell = Rng2.Find(what:=Rng.Cells(i).Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)

	If Not foundcell Is Nothing Then
		Rng.Cells(i).EntireRow.Delete
	End If
	
Next i

The difference here is we move bottom to top, so as we take off rows there's no effect on the rows above which is the direction in which we are going through the loop. For what it's worth, I would typically just add a column, run down a Match() formula, and delete the matched rows -- filtering makes this go pretty quick.

ξ

svkroy · Nov 15, 2011

Thanks a lot ! Worked flawlessly.........Will try the filter option if this takes too much time. Kudos !

xenou said:
I don't know if it matters but deleting rows in a range can sometimes cause issues when you are simultaneously looping the rows (being that parts of the range are disappearing as you go). You could try instead, if Rng is only one column:

Code:

Dim i As Long For i = Rng.Cells.Count to 1 Step -1 '//Assumes Rng is One Column Only Set foundcell = Rng2.Find(what:=Rng.Cells(i).Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) If Not foundcell Is Nothing Then Rng.Cells(i).EntireRow.Delete End If Next i

The difference here is we move bottom to top, so as we take off rows there's no effect on the rows above which is the direction in which we are going through the loop. For what it's worth, I would typically just add a column, run down a Match() formula, and delete the matched rows -- filtering makes this go pretty quick.

ξ

Comparing columns across workbooks !

svkroy

Board Regular

xenou

MrExcel MVP

svkroy

Board Regular

Similar threads

Share this page

Comparing columns across workbooks !

svkroy

Board Regular

xenou

MrExcel MVP

svkroy

Board Regular

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock