Delete Row based on 2 duplicates
 

Hi all,

I have a very large spreadsheet of variable amount of rows and i have a
alphanumeric number in column "G" it is also sorted by this column and then
by column "N" where i have an error code.
What i want to do is if a duplicate in column "G" is found then check column
"N" and if also duplicate then delete the row. e.g.


NB99217 Date Date Date 2 100 5191099000 '<- Do not delete
NB99217 Date Date Date 3 90 6135691500
NB99140 Date Date Date 4 60 5112003600
NB99140 Date Date Date 1 210 5112003600 ' <- Delete
NB99140 Date Date Date 5 10 7260930200 ' Do not delete


I hope i have explained this ok, any help much appreciated...




--
Les

Delete Row based on 2 duplicates
 

Les,

If i've understood correctly, try this. Right click the sheet tab, view code
and paste this in and run it. Note I have commented out the line that does
the delete. If the routine does what you want you will end up with all the
rows for deletion selected. Un comment the delete line when/if you are happy
with it.

Sub copyit()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "G").End(xlUp).Row
Set myrange = Range("G1:G" & lastrow)
For Each c In myrange
If c.Value = c.Offset(1, 0).Value And c.Offset(0, 7).Value = c.Offset(1,
7).Value Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.Offset(1, 0).EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
MyRange1.Select
'Selection.Delete
End Sub

Mike

"Les" wrote:

Hi all,

I have a very large spreadsheet of variable amount of rows and i have a
alphanumeric number in column "G" it is also sorted by this column and then
by column "N" where i have an error code.
What i want to do is if a duplicate in column "G" is found then check column
"N" and if also duplicate then delete the row. e.g.


NB99217 Date Date Date 2 100 5191099000 '<- Do not delete
NB99217 Date Date Date 3 90 6135691500
NB99140 Date Date Date 4 60 5112003600
NB99140 Date Date Date 1 210 5112003600 ' <- Delete
NB99140 Date Date Date 5 10 7260930200 ' Do not delete


I hope i have explained this ok, any help much appreciated...




--
Les

Delete Row based on 2 duplicates
 

Hi, i have used the code supplied by Bob Phillips on an old thread and it
works great ... thanks

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 10 Step -1
If .Cells(i, "G").Value = .Cells(i - 1, "G").Value And _
.Cells(i, "N").Value = .Cells(i - 1, "N").Value And _
.Cells(i, "P").Value = .Cells(i - 1, "P").Value Then
.Rows(i).Interior.Color = RGB(225, 225, 100) 'Delete
End If
Next i
End With


--
Les


"Mike H" wrote:

Les,

If i've understood correctly, try this. Right click the sheet tab, view code
and paste this in and run it. Note I have commented out the line that does
the delete. If the routine does what you want you will end up with all the
rows for deletion selected. Un comment the delete line when/if you are happy
with it.

Sub copyit()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "G").End(xlUp).Row
Set myrange = Range("G1:G" & lastrow)
For Each c In myrange
If c.Value = c.Offset(1, 0).Value And c.Offset(0, 7).Value = c.Offset(1,
7).Value Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.Offset(1, 0).EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
MyRange1.Select
'Selection.Delete
End Sub

Mike

"Les" wrote:

Hi all,

I have a very large spreadsheet of variable amount of rows and i have a
alphanumeric number in column "G" it is also sorted by this column and then
by column "N" where i have an error code.
What i want to do is if a duplicate in column "G" is found then check column
"N" and if also duplicate then delete the row. e.g.


NB99217 Date Date Date 2 100 5191099000 '<- Do not delete
NB99217 Date Date Date 3 90 6135691500
NB99140 Date Date Date 4 60 5112003600
NB99140 Date Date Date 1 210 5112003600 ' <- Delete
NB99140 Date Date Date 5 10 7260930200 ' Do not delete


I hope i have explained this ok, any help much appreciated...




--
Les

Delete Row based on 2 duplicates
 

Delete Row based on 2 duplicates

I'm please you found a solution and not the least bit surprised my code
didn't work when you are now using a routine that deletes rows based upon 3
duplicates. I must learn not to become irritated!!

Mike

"Les" wrote:

Hi, i have used the code supplied by Bob Phillips on an old thread and it
works great ... thanks

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 10 Step -1
If .Cells(i, "G").Value = .Cells(i - 1, "G").Value And _
.Cells(i, "N").Value = .Cells(i - 1, "N").Value And _
.Cells(i, "P").Value = .Cells(i - 1, "P").Value Then
.Rows(i).Interior.Color = RGB(225, 225, 100) 'Delete
End If
Next i
End With


--
Les


"Mike H" wrote:

Les,

If i've understood correctly, try this. Right click the sheet tab, view code
and paste this in and run it. Note I have commented out the line that does
the delete. If the routine does what you want you will end up with all the
rows for deletion selected. Un comment the delete line when/if you are happy
with it.

Sub copyit()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "G").End(xlUp).Row
Set myrange = Range("G1:G" & lastrow)
For Each c In myrange
If c.Value = c.Offset(1, 0).Value And c.Offset(0, 7).Value = c.Offset(1,
7).Value Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.Offset(1, 0).EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
MyRange1.Select
'Selection.Delete
End Sub

Mike

"Les" wrote:

Hi all,

I have a very large spreadsheet of variable amount of rows and i have a
alphanumeric number in column "G" it is also sorted by this column and then
by column "N" where i have an error code.
What i want to do is if a duplicate in column "G" is found then check column
"N" and if also duplicate then delete the row. e.g.


NB99217 Date Date Date 2 100 5191099000 '<- Do not delete
NB99217 Date Date Date 3 90 6135691500
NB99140 Date Date Date 4 60 5112003600
NB99140 Date Date Date 1 210 5112003600 ' <- Delete
NB99140 Date Date Date 5 10 7260930200 ' Do not delete


I hope i have explained this ok, any help much appreciated...




--
Les