Results 1 to 10 of 28

Thread: Remove Duplicates From Across Multipe Columns In A Single WorkSheet

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this

    Code:
    Sub ExcelFox()
            
        Dim lng As Long
        Dim wks As Worksheet
        Dim objDic As Object
        Dim var As Variant
        Dim varIndex As Variant
        Dim lngRow As Long
        Const clngSteps As Long = 100000
        
        Set objDic = CreateObject("Scripting.Dictionary")
        Set wks = Worksheets("NameOfSheetWithDuplicateValues")
        For lng = 1 To 17
            With wks
                .Range(.Cells(1, lng), .Cells(.Rows.Count, lng).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlNo
                var = .Range(.Cells(1, lng), .Cells(.Rows.Count, lng).End(xlUp)).Value2
                .Range(.Cells(1, lng), .Cells(.Rows.Count, lng).End(xlUp)).Clear
                For lngRow = 1 To UBound(var)
                    objDic.Item(var(lngRow, 1)) = 0
                Next lngRow
            End With
        Next lng
        Erase var
        var = objDic.keys
        Set objDic = Nothing
        If wks.Parent.FullName <> wks.Parent.Name Then
            wks.Parent.Save
        End If
        lng = 1
        For lngRow = 1 To UBound(var) + 1 Step clngSteps
            varIndex = Evaluate(clngSteps * (lng - 1) & "+ ROW(1:" & Application.Min(UBound(var) + 1, clngSteps) & ")")
            Cells(1, lng).Resize(Application.Min(UBound(var) + 1, clngSteps)).Value = Application.Index(var, varIndex)
            lng = lng + 1
        Next lngRow
        Erase varIndex
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Junior Member
    Join Date
    Oct 2013
    Posts
    14
    Rep Power
    0

    First result

    Thank you so much for your effort. The script ran for 2 hours then crashed at below line:

    Cells(1, lng).Resize(Application.Min(UBound(var) + 1, clngSteps)).Value = Application.Index(var, varIndex)

    Error message said 'mismatch error'

    also, the sheet has been cleared of all data - if it's of any help to you.

    Kind Regards

Similar Threads

  1. Find Duplicates, 2 Columns, Different Worksheets
    By ghendi in forum Excel Help
    Replies: 14
    Last Post: 07-17-2013, 04:26 AM
  2. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  3. Print Nth Worksheet To Mth Worksheet using VBA
    By Ryan_Bernal in forum Excel Help
    Replies: 2
    Last Post: 02-28-2013, 06:57 PM
  4. Converge Data From Multiple Columns To Single Column
    By ayazgreat in forum Excel Help
    Replies: 3
    Last Post: 12-14-2012, 10:55 PM
  5. Detect Duplicates In Named Ranges With Cross Check
    By Yegarboy in forum Excel Help
    Replies: 3
    Last Post: 10-09-2012, 11:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •