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
|
Similar Threads
