Delete duplicate rows depending on Column A - HELP!!

[Babykins]

Hi all,

I'm sure this is desperately simple, but as they say, nothing's easy unless you know the answer, and I don't!

I have a spreadsheet with loads of data on it. In column A is the serial code column, and I have been finding duplicates down it. What I want is some macro code that will look down column A and if it finds any duplicate serial numbers, it will delete that entire row.

For instance if it finds 6 lots of 111222, then I want it to delete 5 of them.

Can anyone help me?

Thank you so much

Janie
xxxx

 

[kskinne]

hello and welcome to the board

asap utilities is a free add-in available at http://www.asap-utilities.com

this add-in has many useful features, and the one that would be useful to you in this instance is the conditional row/column select/hide/delete option, which you can use to delete the duplicate rows in a large range of data. post back if you download and install this, and have any questions about how to use the add-in to accomplish your task.

hth
kevin

 

[Babykins]

Hi, sounds great but we are unable to download and install anything here. Would anyone be able to supply me with some code that I can write into a module? I'm sure I've seen it here before but I can't seem to find the post, despite having searched.

I'd be most grateful for some help

Janie

 

[lenze]

Here is the code from Chip Pearson's site:

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection.
' Duplicates are counted in the COLUMN of the active cell.

Dim Col As Integer
Dim R As Long
Dim C As Range
Dim n As Long
Dim V As Variant
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.rows
End If

n = 0
For R = rng.rows.Count To 1 Step -1
V = rng.Cells(R, 1).Value
If Application.WorksheetFunction.CountIf(rng.Columns(1), V) > 1 Then
rng.rows(R).EntireRow.Delete
n = n + 1
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Babykins]

Thanks, I'll go and try it now

xx

 

[edyong]

1. Insert a row and type a heading called Serial (eg. A1)
2. Type in Flag at B1
3. Sort the Serial in ascending order
4. At B2, key in if(a2=a3,char(160),"Last") and copy down the formula.
5. Highlight A1:B1 and click on Data-->Filter-->Auto-Filter.
6. Filter the Flag to see only "Last"
7. Highlight all the "unique" Serial numbers, and click Copy and Paste-Special --> Value onto another column or worksheet.

Hope it works for u ...