Delete row if double

B

bahri

Hi all!
I have a sheet with 10 columns, and data is pasted (added) to it daily.
Sometimes there are duplicates, which I want to delete.
I am trying to write a macro that checks if A1 and B1 of the pasted row
already exist (after pasting), and if so delete the whole row.

Another macro I need is a pasting macro for these rows that pastes at the
bottom first empty row that exists.

Thanks for all the info here

bahri
 
O

Otto Moehrbach

To find the first empty cell in Column A, call it Dest, use something like
this:
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1))
To find out if the last entries in Column A & B are duplicates of something
above that row, and to delete that row is so, use something line this:
Sub DeleteDup()
Dim RngColA As Range
Dim LastA As Range
Dim LastB As Range
Set LastA = Range("A" & Rows.Count).End(xlUp)
Set LastB = LastA.Offset(, 1)
Set RngColA = Range("A1", LastA.Offset(-1))
If Not RngColA.Find(What:=LastA.Value, LookAt:=xlWhole) Is Nothing Then
_
If RngColA.Find(What:=LastA.Value, _
LookAt:=xlWhole).Offset(, 1).Value = _
LastB.Value Then LastA.EntireRow.Delete
End Sub
HTH Otto
 
B

bahri

Hi!
Thanks a lot, will try things out.

bahri


Otto Moehrbach said:
To find the first empty cell in Column A, call it Dest, use something like
this:
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1))
To find out if the last entries in Column A & B are duplicates of
something above that row, and to delete that row is so, use something line
this:
Sub DeleteDup()
Dim RngColA As Range
Dim LastA As Range
Dim LastB As Range
Set LastA = Range("A" & Rows.Count).End(xlUp)
Set LastB = LastA.Offset(, 1)
Set RngColA = Range("A1", LastA.Offset(-1))
If Not RngColA.Find(What:=LastA.Value, LookAt:=xlWhole) Is Nothing Then
_
If RngColA.Find(What:=LastA.Value, _
LookAt:=xlWhole).Offset(, 1).Value = _
LastB.Value Then LastA.EntireRow.Delete
End Sub
HTH Otto
Click to expand...
 
Top