Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Macro to delete rows with same data
 
 
LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #10   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 With QuoteReply With Quote
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Threaded Mode Threaded Mode

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 08: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 04:53 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement