Comparing two columns on different worksheets

[bobkrieg]

Hey everyone,

Im new to VBA programming, I was asked to help speed up some processes at work and I am lost about what to do next.

The situation is that I have to compare two columns on different worksheets, both are account numbers. I have to compare one to the other and if the account appears on both worksheets, I have to delete one of the rows holding the account number... for example

Account # (worksheet 1) Account # (worksheet 2)
123 123
323 344
455 468
222 323

and so forth...

I don't have my work so far to show what I have tried to do, but so far I have tried looping a program to read the Cell in worksheet 1 and compare it to worksheet 2, and delete the Row in worksheet 1 if the value appears anywhere on worksheet 2's column. I figured it might have something to do with arrays, but I am not sure how to set it up or what sort of process i can use. Is there any way i can check a single cell value from worksheet 1 to the entire array of the column in worksheet 2?


I figured if that worked, a macro could recognize if the number is on worksheet two and i can have an IF THEN statement where the row deletes itself. Does that sound right? ANy ideas about how to get the single value to check against every number in an array? or just against the column in general?

thanks

 

[SkipVought]

Hi,

I do this quite often using the MATCH function.

If it returns NA then there's no match. A match retuns a number.
[tt]
=if)isna(match(a1,Sheet2!A:A,0)),0,1)

[/tt]
filter on the 1's and delete rows.

Skip,

_[red][/red]

 

[bobkrieg]

thanks skip, ill try that out tomorrow at work. if i have any problems ill let you know!

 

[SkipVought]

oops...
[tt]
=if(isna(match(a1,Sheet2!A:A,0)),0,1)
[/tt]


Skip,

_[red][/red]

 

[bobkrieg]

is there any way you could help me put that code into VBA?

here's something ive pieced together so far but it doesnt work, but its got the gist of what im going for. can anyone help? i keep getting the "Error 1004" message... but its late and im not very good at this yet so it could be something im missing...



Sub rowComparison()

Dim rowNum As Integer
Dim colNum As Integer
Dim CellA As Range


rowNum = 1
colNum = 1



Set CellA = ActiveSheet.Range(1, 1)

Do
If Application.Match(CellA, Range("Sheet2A1:A8"), 0, 1, 0) = 1 Then
Rows(rowNum).Delete
End If

rowNum = rowNum + 1

Set CellA = ActiveSheet.Range(rowNum, 1)

Loop While CellA <> ""

End Sub


Basically I want to delete a Row if the value in CellA whatever cell it may be at the time of the loop) appears at all in the column that I am comparing it to on another worksheet.

 

[SkipVought]

Do it on the sheet, recording with the macro recorder.

Then post back if you need help.

Skip,

_[red][/red]

 

[SkipVought]

BTW, It is NOT the best way to LOOP and DELETE. If you do DELETE in a LOOP, it must be done from the Bottom up. Otherwise, you destroy your current row reference.

The Better method is to FILTER to display the rows to delete and DELETE the visible rows of DATA.

Skip,

_[red][/red]