Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Macro to delete rows with same data

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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

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





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Macro to delete rows with same data

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







  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

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










  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Macro to delete rows with same data

Connie,

Change this bit, from

MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

to

MatchCase:=False, Orientation:=xlTopToBottom

HTH,
Bernie
MS Excel MVP


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










  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

I now get a error pop-up message "400". Just to confirm, this is how the
macro looks now:

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

"Bernie Deitrick" wrote:

Connie,

Change this bit, from

MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

to

MatchCase:=False, Orientation:=xlTopToBottom

HTH,
Bernie
MS Excel MVP


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











  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Macro to delete rows with same data

Connie

You don't need a macro for this.

You can use DataFilterAdvanced Filter to select unique records.

See Debra Dalgleish's site for more info.

http://www.contextures.on.ca/xladvfi....html#FilterUR

If you want a macro, record one while going through the steps.


Gord Dibben Excel MVP


On Fri, 18 Nov 2005 08:35:46 -0800, "Connie Martin"
wrote:

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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Macro to delete rows with same data

Connie,

That code, copied straight into a module, worked fine for me.

There is one possible source of error: the web interface often does strange things to code when
converting to html. Post your email, and I will send you a working workbook.

HTH,
Bernie
MS Excel MVP


"Connie Martin" wrote in message
...
I now get a error pop-up message "400". Just to confirm, this is how the
macro looks now:

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

"Bernie Deitrick" wrote:

Connie,

Change this bit, from

MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

to

MatchCase:=False, Orientation:=xlTopToBottom

HTH,
Bernie
MS Excel MVP


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













  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

I wish I could record the macro but this one is different and I don't know
how to record it. I had to do it manually because I had to get the job done
before the end of the day. So, it's all done now. Some of the worksheets
were over 2000 rows. However, I sure would like to know how to do it for
future projects. I will read on up the link you sent. I have used the
filter but not on something like this.

"Gord Dibben" wrote:

Connie

You don't need a macro for this.

You can use DataFilterAdvanced Filter to select unique records.

See Debra Dalgleish's site for more info.

http://www.contextures.on.ca/xladvfi....html#FilterUR

If you want a macro, record one while going through the steps.


Gord Dibben Excel MVP


On Fri, 18 Nov 2005 08:35:46 -0800, "Connie Martin"
wrote:

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





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

I don't think Advanced Filter is going to work for this. I want unique
records per each group. Each group is divided by an empty row. ME21N may
show up three times in one group and needs two of them deleted, but ME21N can
be in several other groups only once and it's not to be deleted. So, by
filtering using the "unique records only", I can't see how it can work.
Anyway, as I said, this project is now done--done painfully manually. The
week is ended. I'm going home. Thank you for your input. Connie

"Gord Dibben" wrote:

Connie

You don't need a macro for this.

You can use DataFilterAdvanced Filter to select unique records.

See Debra Dalgleish's site for more info.

http://www.contextures.on.ca/xladvfi....html#FilterUR

If you want a macro, record one while going through the steps.


Gord Dibben Excel MVP


On Fri, 18 Nov 2005 08:35:46 -0800, "Connie Martin"
wrote:

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



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Connie Martin
 
Posts: n/a
Default Macro to delete rows with same data

Just getting back to looking at this now. Thank you for your help. You can
use this address:

"Bernie Deitrick" wrote:

Connie,

That code, copied straight into a module, worked fine for me.

There is one possible source of error: the web interface often does strange things to code when
converting to html. Post your email, and I will send you a working workbook.

HTH,
Bernie
MS Excel MVP


"Connie Martin" wrote in message
...
I now get a error pop-up message "400". Just to confirm, this is how the
macro looks now:

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

"Bernie Deitrick" wrote:

Connie,

Change this bit, from

MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

to

MatchCase:=False, Orientation:=xlTopToBottom

HTH,
Bernie
MS Excel MVP


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














  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Macro to delete rows with same data

Connie,

Sent.

HTH,
Bernie
MS Excel MVP


"Connie Martin" wrote in message
...
Just getting back to looking at this now. Thank you for your help. You can
use this address:

"Bernie Deitrick" wrote:

Connie,

That code, copied straight into a module, worked fine for me.

There is one possible source of error: the web interface often does strange things to code when
converting to html. Post your email, and I will send you a working workbook.

HTH,
Bernie
MS Excel MVP


"Connie Martin" wrote in message
...
I now get a error pop-up message "400". Just to confirm, this is how the
macro looks now:

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

"Bernie Deitrick" wrote:

Connie,

Change this bit, from

MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

to

MatchCase:=False, Orientation:=xlTopToBottom

HTH,
Bernie
MS Excel MVP


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
















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete blank rows Macro Richard Excel Discussion (Misc queries) 3 November 4th 05 09:02 AM
Closing File Error jcliquidtension Excel Discussion (Misc queries) 4 October 20th 05 12:22 PM
Dealing with data in several columns AND rows RJPerri Excel Discussion (Misc queries) 2 September 14th 05 12:57 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
inserting rows through external data source [email protected] Excel Discussion (Misc queries) 0 April 5th 05 03:16 AM


All times are GMT +1. The time now is 01:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"