VB code required to compare/delete data

[Guest]

Hi all,

Can somebody donate some VB code to carry out the following task?

Look at name in cell BC2, check the name in cell BA2 and if is the same
delete the name in BA2 and BC2.
If is different then leave both cells as they are and move onto checking BC3
against BA3
carry this loop thru BC2 : BC40

hope this makes sense and thanks in advance

 

[Tom Ogilvy]

Sub ABC()
dim i as Long
for i = 40 to 2 step -1

if not isempty(cells(i,"BC")) then
if cells(i,"BC").Value = cells(i,"BA").Value then
' rows(i).Delete
Cells(i,"BC").ClearContents
Cells(i,"BA").ClearContents
end if
End if
Next i
End Sub

 

[Guest]

Hi Tom

Thanks for the above, but after running the macro, nothing seems to happen,
ie no names are removed from the list in column BC when it is obvious they
are also listed in column BA !

 

[Tom Ogilvy]

The code doesn't lie Anthony. Your eyes might or you might not have
correctly described your situation.

I tested the code and it worked fine for me.

Let's assume you have some spaces after the data in one column that are not
in the other.

Sub ABC()
Dim i As Long
For i = 40 To 2 Step -1

If Not IsEmpty(Cells(i, "BC")) Then
If Trim(lcase(Cells(i, "BC").Value)) = _
Trim(lcase(Cells(i, "BA").Value)) Then
' rows(i).Delete
Cells(i, "BC").ClearContents
Cells(i, "BA").ClearContents
End If
End If
Next i
End Sub

If that doesn't work, go to

BD2 and put in the formula

=BC2=BA2
and drag fill down the column. I would expect all to be false.