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

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


Anthony wrote:

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

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

--
Regards,
Tom Ogilvy


"Anthony" wrote in message
...
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

One of us read it wrong <g

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
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


Anthony wrote:

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

On Feb 6, 12:42 am, "Tom Ogilvy" wrote:
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

--
Regards,
Tom Ogilvy

"Anthony" wrote in message

...

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

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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Anthony" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"lc61800" wrote:

On Feb 6, 12:42 am, "Tom Ogilvy" wrote:
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

--
Regards,
Tom Ogilvy

"Anthony" wrote in message

...

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

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

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

Tom Ogilvy wrote:

One of us read it wrong <g

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
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


Anthony wrote:

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

--

Dave Peterson