Removing Duplicate Rows BASIC VBA

[ShelleyBelly]

I'm new to VBA but have been browsing the web for sometime now trying to find a macro that can do a simple remove duplicates function on an active selection of cells.

for example:

1--2--3
1--2--3
2--4--5
5--3--2
4--1--3
5--3--2

should become

1--2--3
2--4--5
5--3--2
4--1--3

I have tried the VBA in

http://www.mrexcel.com/forum/showthread.php?t=321140&highlight=basic+remove+duplicates+vba

but this only seems to remove one row at a time so if there are 3 identical rows the script will have to be run twice.

I would also like it to work for any sized array.

I may have dropped the ball as i'm sure someone must have done this before so if you know where please do redirect me.

Many Thanks,

Tom

 

[cgcamal]

Hi Tom,

In a copy of your original sheet try with:

[COLOR=Navy]Sub [/COLOR]Delete_Repeated_Rows()
[COLOR=Green]'César C 05/May/2011
'Delete repeated rows[/COLOR]

Dim Cu() As Variant
Dim Lr As Long
Dim Lc As Long

Application.ScreenUpdating = False
    With ActiveSheet.UsedRange
        Lr = .Rows(.Rows.Count).Row
    End With
    
    With ActiveSheet.UsedRange
        Lc = .Columns(.Columns.Count).Column
    End With
    
    ReDim Cu(1 To Lr)
    
    For k = 1 To Lr
        For j = 1 To Lc
            Cu(k) = Cu(k) & "&" & Cells(k, j)
        Next
    Next

    Cells(1, Lc + 1) = "Unique"
    For j = 1 To Lr
        Cells(j + 1, Lc + 1) = Cu(j)
    Next
    
    Range(Cells(1, Lc + 1), Cells(Lr + 1, Lc + 1)).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Cells(1, Lc + 2), Unique:=True
    
    Cells(1, Lc + 2).Delete Shift:=xlUp
    
        Range(Cells(1, Lc + 2), Cells(Lr + 1, Lc + 2)).TextToColumns Destination:=Cells(1, Lc + 2), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :="&", TrailingMinusNumbers:=True
        
        Range(Cells(1, 1), Cells(Lr + 1, Lc + 2)).Delete Shift:=xlToLeft
        
Application.ScreenUpdating = True
[COLOR=Navy]End Sub[/COLOR]

Hope this helps,

Regards

 

[brettdj]

My Duplicate Master addin will do this, available at http://www.experts-exchange.com/A_2123.html

- When you run the addin the active selection will be the default range (in your example A1:C6)
- Then pick the 'Row Search' option

This will delete any duplicate rows where A, B & C are common, irrespective of what may be in D, E etc

Cheers

Dave

 

[mirabeau]

You may like to peruse this thread

http://www.mrexcel.com/forum/showthread.php?t=464213&page=1

which discusses in some detail the sort of problem you are considering.

In addition to discussion, there's a number of methods and codes suggested for deleting duplicated rows. These methods generally get faster in the later posts of the thread, particularly if you're dealing with large problems.

As one point noted, the advanced filter is generally very good and fast for problems up to about 30,000 rows, but beyond about 40,000 it can often become virtually useless.