| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to delete rows with same data
|
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro to delete rows with same data
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete blank rows Macro | Excel Discussion (Misc queries) | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Dealing with data in several columns AND rows | Excel Discussion (Misc queries) | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| inserting rows through external data source | Excel Discussion (Misc queries) | |||
Linear Mode
