Deleting duplicate data in Excel 2

[Lukin]

I have about 400 rows of data in an Excel spreadsheet which I've imported from an AS/400. The data is sorted by Column A which contains Operation Numbers. In some cases there may be 5 or 6 rows with the same Operation number.

I want to write some VBA code to delete all the rows which contain duplicate Operation Numbers, leaving only the top one. I had the idea of using a FOR...NEXT loop and an IF statement to cycle through each row in turn, deleting the row if the Operation Number matched that of the row above.

Could someone tell me how I would go about finding which is the last row which contains data so that I can use this figure for my FOR...NEXT loop?

BTW, if I'm going about this the long way round please let me know!!!

 

[longhair]

Lukin,

try this (untested)

sub delrow
Dim astring
Dim bstring
Dim counter As Integer
counter = 0
Do Until counter = 16000
    counter = counter + 1
    astring = ActiveCell.Value
    ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
    bstring = ActiveCell.Value
    If astring = bstring Then Selection.EntireRow.delete
    Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
    End If
Loop
End Sub

just change the counter to your number of rows or change the do to run until it finds an empty cell.
hth
regards,
longhair

 

[DanAuber]

I normally deal with things like this by sorting the data by the column that might be a duplicate (A in this example) and then using a formula in a free column(eg B in this example) like this:

=if(A1=A2,"DUP","SIN")

start this in B2 and copy it down.

Then copy paste special values the B column

Sort by B and all the ones with DUP are duplicates

Dan Auber

 

[arthurbr]

This thread ( last answer)

thread68-940355

will give you an answer

 

[Lukin]

Thanks Longhair, I had to make a couple of amendments to your code but to say it was untested that was excellent .

Thanks DanAuber and arthurbr. I think both of your solutions would have worked but I specifically wanted to do this in VBA so that a user could run it as a fool-proof (hopefully) macro.

 

[longhair]

Lukin,
glad to be of help.
regards,
longhair

 

[byundt]

Lukin,
When you loop through the rows looking for duplicates, deleting the row will throw the row counter off for the next iteration. To avoid this issue, either loop through the rows backwards or perform the deletion after having completed your check.

In case you didn't find and fix this issue with the previously posted code, then here is a new sub. It will also run faster and without screen flicker, because it doesn't activate or select any cells.

Sub NoDupes()
Dim sRef As String
Dim cel As Range, rgDelete As Range
Set rgDelete = Cells(65536, 1)      'No data there, so safe to delete it
For Each cel In Range([A2], [A65536].End(xlUp))       'Check column A for duplicates, starting in row 2
    If cel = cel.Offset(-1, 0) Then Set rgDelete = Union(rgDelete, cel)
Next
rgDelete.EntireRow.Delete
End Sub

Brad

 

[Lukin]

Thanks byundt!