VB code required to compare name list

[Guest]

Hi all,

I would like some ideas of VB code that would take a name from cell A1 and
compare it with the name in B1.
If it is the same delete both A1 and B1 values, if it is different compare
against B2, delete A1 and B2 if name is same....etc etc

Columns A and B have a range of 15 rows

thanks

 

[Dave Peterson]

Option Explicit
Sub Testme()
dim iRow as long

'start from the bottom and work up
with activesheet
for irow = 15 to 1 step -1
if .cells(irow,"A").value = .cells(irow,"B").value then
.rows(irow).delete
end if
next irow
end with

End Sub

 

[Tom Ogilvy]

Delete or clear the cell?

Sub aA()
Dim cell As Range, res As Variant

For Each cell In Range("A1:A15")
res = Application.Match(cell, Range("B1:B15"), 0)
If Not IsError(res) Then
Cells(res, 2).ClearContents
cell.ClearContents
End If
Next

' if you want to delete then do

Range("A1:B15").SpecialCells(xlBlanks).Delete shift:=xlShiftUp


End Sub

 

[Tom Ogilvy]

One of us read it wrong <g>

 

[lc61800]

Delete or clear the cell?

Sub aA()
Dim cell As Range, res As Variant

For Each cell In Range("A1:A15")
res = Application.Match(cell, Range("B1:B15"), 0)
If Not IsError(res) Then
Cells(res, 2).ClearContents
cell.ClearContents
End If
Next

' if you want to delete then do

Range("A1:B15").SpecialCells(xlBlanks).Delete shift:=xlShiftUp

End Sub

Hello,

I've tried your code and it works OK for deleting cells. That's very
useful to me. Thanks.

Does not seem to be working for deleting rows but I am probably wrong
since I am a complete newbie. Got error message 1004 "No cells were
found" even though there are rows with identical values. I probably
mistyped something below.

Best regards,
Laurent

Dim cell As Range, res As Variant

For Each cell In Range("A1:A15")
res = Application.Match(cell, Range("B1:B15"), 0)
If Not IsError(res) Then
Range("A1:B15").SpecialCells(xlBlanks).Delete shift:=xlShiftUp
End If
Next

 

[Guest]

Many thanks to you both for the excellent reply
Really appreciate your help
regards

 

[Guest]

the code wasn't written to delete rows. Maybe see Dave Peterson's post in
this thread.

 

[Dave Peterson]

Or one of us read more into it--and that's what the OP wanted (still a maybe on
that one, though!)