Re: How do I remove Duplicate rows?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Ardus,

Thank you for this code, however, when I tested it, it deletes random cells
of duplicate data which messes up the data in relation to the row. I need it
to look at the data as a row and delete the entire row if it is a duplicate
row. Also, the duplicates may not be side-by-side.

Thanks for your help!

Lost

"Ardus Petus" wrote:

Here is the code (not fully tested)

HTH

--
AP

'----------------------------------
Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer
Dim lignesEgales As Boolean
Dim ligneRange As Range
Dim c As Range
'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1", Cells(Numrows, "B")).Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
Set ligneRange = Range("A1:R1").Offset(Iloop - 1, 0)
lignesEgales = True
For Each c In ligneRange
If c.Value <> c.Offset(-1, 0).Value Then
lignesEgales = False
Exit For
End If
Next c
If lignesEgales Then Rows(Iloop).Delete
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'----------------------------------

"Lost in Alabama" <LostinAlabama@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le
message de news:AAC4CC2A-3DBC-45A0-8653-BDB5D0824C1C@xxxxxxxxxxxxxxxx
I need to check from A:R.

Thanks,

Lost In ALabama

"Ardus Petus" wrote:

Could you specify on how many (or which) columms you want to check for
duplicates ?
There HAS to be a limit on the number of columns you want to check.

Such as it is, your macro will only detect duplicates on rows A & B

Cheers,
--
AP

"Lost in Alabama" <LostinAlabama@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans
le
message de news:4DDA3CFA-9A23-435D-8429-7D5E5FCBEEBA@xxxxxxxxxxxxxxxx
Hello Ken,

I am Lost in Alabama and I have a question about this post as my
situation
is different than the orginal post.

How would I modify this code if the duplicate data is by the entire
row.
In
other words the duplicates will be in varing columns.

Thanks,

Lost In Alabama

"Ken Hudson" wrote:

Here is a macro solution, assuming that row 1 has headers.
Right click on any worksheet (tab) at the bottom of your workbook.
Select View Code.
Go to Insert>Module and copy and paste the code from below into the
module.
Close the visual basic editor.
Back at your workbook go to Tools>Macro>Macros... and run the macro.
Be sure to save a copy of your workbook beforehand.

---------------------------------------------------

Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:B" & Numrows).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") + Cells(Iloop, "B") = Cells(Iloop - 1, "A")
+ _
Cells(Iloop - 1, "B") Then
Rows(Iloop).Delete
End If
Next Iloop

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

--
Ken Hudson


"85225" wrote:

I have an excel spreadsheet that has 2 columns. Col A is customer#
and
Col B
is invoice #.
A customer# can have many invoice#s
A customer# can have many rows with the same inv#.
An invoice# can only be associated with 1 customer#

I need to create a spreadsheet that shows each customer with the
associated
invoice#. Each customer#/invoice# should only show once.

for example:

cust# invoice#
1 100
1 100
1 101
2 200
3 300
3 300


The result would be:

cust# inv#
1 100
1 101
2 200
3 300

How do I get rid of the extra rows?










.

Relevant Pages