Is there a macro to identify and remove duplicate data in Excel?

[Cindy Lou]

I need to remove duplicate rows in a spreadsheet. These spreadsheets are
created several times each day and I currently have to remove the duplicate
rows manually. This can take a considerable amount of time. Is there a
macro or function that can identify and remove these duplicate rows?

[Otto Moehrbach]

Cindy
I assume that by "duplicate rows" you mean that the value (entry) in one
column is duplicated in another cell in the same column, instead of the
entire row must be a duplicate. If this is correct, then the following
macro will do what you want.
I assumed, in writing this macro, that Column A is the column to look at.
Watch out for line wrap in this message, as that would effect the macro.
Expand this message to see the macro as it should be.
Make a copy of your file and try this out on the copy first. Post back if
this macro doesn't do what you want. HTH Otto
Sub RemoveDups()
Dim RngA As Range
Dim c As Long
Set RngA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For c = RngA.Count To 1 Step -1
If RngA.Find(What:=Cells(c, 1).Value, After:=Cells(c, 1),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext).Row > c Then
Cells(c, 1).EntireRow.Delete
End If
Next c
End Sub
"Cindy Lou" <Cindy Lou@discussions.microsoft.com> wrote in message
news:5A7E6AF8-6A8A-4521-A1AE-5D3B194B3B2A@microsoft.com...
>I need to remove duplicate rows in a spreadsheet. These spreadsheets are
> created several times each day and I currently have to remove the
> duplicate
> rows manually. This can take a considerable amount of time. Is there a
> macro or function that can identify and remove these duplicate rows?

[Lost in Alabama]

Hello Otto,

I have a situation similar to this one, except the duplicates I want to
delete are by the Row (not by just one Cell). In other words, I need the
macro to look at the entire row for a duplicate. Also, the duplicate rows
may not be together and I cannot sort the data without loosing the original
order.

Is it possible to alter this macro to perform this function, or do you have
another suggestion?

Thanks,

Lost in Alabama

"Otto Moehrbach" wrote:

> Cindy
> I assume that by "duplicate rows" you mean that the value (entry) in one
> column is duplicated in another cell in the same column, instead of the
> entire row must be a duplicate. If this is correct, then the following
> macro will do what you want.
> I assumed, in writing this macro, that Column A is the column to look at.
> Watch out for line wrap in this message, as that would effect the macro.
> Expand this message to see the macro as it should be.
> Make a copy of your file and try this out on the copy first. Post back if
> this macro doesn't do what you want. HTH Otto
> Sub RemoveDups()
> Dim RngA As Range
> Dim c As Long
> Set RngA = Range("A1", Range("A" & Rows.Count).End(xlUp))
> For c = RngA.Count To 1 Step -1
> If RngA.Find(What:=Cells(c, 1).Value, After:=Cells(c, 1),
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, SearchOrder:=xlByColumns,
> SearchDirection:=xlNext).Row > c Then
> Cells(c, 1).EntireRow.Delete
> End If
> Next c
> End Sub
> "Cindy Lou" <Cindy Lou@discussions.microsoft.com> wrote in message
> news:5A7E6AF8-6A8A-4521-A1AE-5D3B194B3B2A@microsoft.com...
> >I need to remove duplicate rows in a spreadsheet. These spreadsheets are
> > created several times each day and I currently have to remove the
> > duplicate
> > rows manually. This can take a considerable amount of time. Is there a
> > macro or function that can identify and remove these duplicate rows?
>
>
>

[Otto Moehrbach]

Yes, the basic macro can be used for your case too. But it would have to be
modified to include a looping routine through all the cells in the row.
Send me an email with as much explanation as you can give. You might
include a small file that shows a sample of what you have. My email address
is ottokmnop@comcast.net. Remove the "nop" from this address. Otto
"Lost in Alabama" <LostinAlabama@discussions.microsoft.com> wrote in message
news:5E206764-B5DC-48F9-9568-78B11A7E7BD4@microsoft.com...
> Hello Otto,
>
> I have a situation similar to this one, except the duplicates I want to
> delete are by the Row (not by just one Cell). In other words, I need the
> macro to look at the entire row for a duplicate. Also, the duplicate rows
> may not be together and I cannot sort the data without loosing the
> original
> order.
>
> Is it possible to alter this macro to perform this function, or do you
> have
> another suggestion?
>
> Thanks,
>
> Lost in Alabama
>
> "Otto Moehrbach" wrote:
>
>> Cindy
>> I assume that by "duplicate rows" you mean that the value (entry) in one
>> column is duplicated in another cell in the same column, instead of the
>> entire row must be a duplicate. If this is correct, then the following
>> macro will do what you want.
>> I assumed, in writing this macro, that Column A is the column to look at.
>> Watch out for line wrap in this message, as that would effect the macro.
>> Expand this message to see the macro as it should be.
>> Make a copy of your file and try this out on the copy first. Post back
>> if
>> this macro doesn't do what you want. HTH Otto
>> Sub RemoveDups()
>> Dim RngA As Range
>> Dim c As Long
>> Set RngA = Range("A1", Range("A" & Rows.Count).End(xlUp))
>> For c = RngA.Count To 1 Step -1
>> If RngA.Find(What:=Cells(c, 1).Value, After:=Cells(c, 1),
>> LookIn:=xlFormulas, _
>> LookAt:=xlWhole, SearchOrder:=xlByColumns,
>> SearchDirection:=xlNext).Row > c Then
>> Cells(c, 1).EntireRow.Delete
>> End If
>> Next c
>> End Sub
>> "Cindy Lou" <Cindy Lou@discussions.microsoft.com> wrote in message
>> news:5A7E6AF8-6A8A-4521-A1AE-5D3B194B3B2A@microsoft.com...
>> >I need to remove duplicate rows in a spreadsheet. These spreadsheets
>> >are
>> > created several times each day and I currently have to remove the
>> > duplicate
>> > rows manually. This can take a considerable amount of time. Is there
>> > a
>> > macro or function that can identify and remove these duplicate rows?
>>
>>
>>