I get a Compile Error message---"named argument not found". The part
highlighted are the words "DataOption1:=" in the 9th line from the bottom.

"Bernie Deitrick" wrote:

Connie,

Select your entire table, including the headers at the top, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim myRange As Range
Dim myCell As Range
Dim myCol As Integer
Dim myForm As String
Dim i As Integer

Set myRange = Selection

myRange(2, myRange.Columns.Count + 1).Resize(1, 3). _
EntireColumn.Insert

myForm = "=RC[-" & myRange.Columns.Count & "]"
For i = myRange.Columns.Count - 1 To 1 Step -1
myForm = myForm & " & RC[-" & i & "]"
Next i

myRange(2, myRange.Columns.Count + 1). _
Resize(myRange.Rows.Count, 1).FormulaR1C1 = myForm
myRange(2, myRange.Columns.Count + 2). _
Resize(myRange.Rows.Count, 1).FormulaR1C1 = _
"=COUNTIF(R" & myRange(2).Row & "C" & _
myRange(1, myRange.Columns.Count + 1) & ":RC[-1],RC[-1])"
myRange.CurrentRegion.Sort Key1:=myRange(2, _
myRange.Columns.Count + 2), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Set myCell = myRange(2, myRange.Columns.Count + 2). _
Resize(myRange.Rows.Count, 1). _
Find(What:="2", LookIn:=xlValues)
Intersect(myRange, Range(myCell, myCell.End(xlDown)). _
EntireRow).Delete shift:=xlUp
myRange(2, myRange.Columns.Count + 1).Resize(1, 3). _
EntireColumn.Delete
End Sub


"Connie Martin" wrote in message
...
Good question. I hadn't thought of that. It would be the repeat numbers in
column D which repeat for each group in column C. For example, the ones with
an X to the far right would be deleted:

A B C D E
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 ME22N X
MACLEOL Lynn MacLeod P0040104 ME22N X
MACLEOL Lynn MacLeod P0040104 MR8M

MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 MB0A X
MACLEOL Lynn MacLeod P0050006 MB0A X
MACLEOL Lynn MacLeod P0050006 ME21N
MACLEOL Lynn MacLeod P0050006 ME21N X
MACLEOL Lynn MacLeod P0050006 ME21N X

MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 ME21N X
MACLEOL Lynn MacLeod P0050008 ME21N X
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO X
MACLEOL Lynn MacLeod P0050008 MIGO X
MACLEOL Lynn MacLeod P0050008 MIGO X




"Bernie Deitrick" wrote:

Connie,

Should the second of these ME21N's be deleted, or does the P0050006/8 difference mean that it
should
be kept?

MACLEOL Lynn MacLeod P0050006 ME21N
MACLEOL Lynn MacLeod P0050008 ME21N

HTH,
Bernie
MS Excel MVP


"Connie Martin" wrote in message
...
I need another macro! Boy, I wish I knew how to write these wonderful
things! In the sample below I need a macro that will delete the entire rows
where the data is repeated in a given column---that is, that it would delete
the repeat ones, leaving one of them. In this example, in the first set, all
that should be left are two rows which contain ME22N and MR8M in column E.
ME22N is repeated two more times and those rows need to be deleted. If I
could get a macro that would ask for the column to operate in, that would be
great for other spreadsheets where it would not necessarily be column E.

A B C D E
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 ME22N
MACLEOL Lynn MacLeod P0040104 MR8M

MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 MB0A
MACLEOL Lynn MacLeod P0050006 ME21N
MACLEOL Lynn MacLeod P0050006 ME21N
MACLEOL Lynn MacLeod P0050006 ME21N

MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 ME21N
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO
MACLEOL Lynn MacLeod P0050008 MIGO