Excel Hints

Results 1 to 10 of 10

Thread: Solved: how to highlight duplicate ROWS in excel

  1. #1

    Solved: how to highlight duplicate ROWS in excel

    I want to highlight duplicate rows in excel to be highlighted

    Code should check for entire row matching

  2. #2
    Quote Originally Posted by v_gyku
    I want to highlight duplicate rows in excel to be highlighted

    Code should check for entire row matching
    do you wish to highlight all duplicated rows in the sheet or just those that have been selected?
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Welcome to VBAX!

    I've moved your thread to the Excel Forum.
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  4. #4
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by brettdj
    Used it some time ago!

    Nice peace of work Dave!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  6. #6
    HI sir,

    I visited the link u have given
    I got that duplicate rows example.
    But i want to wri te the code for it, Can u give me the code..
    It is asking for password when i am trying to open vbaproject (duplicatemaster.xla)
    please can u help me

    I want that example where in it is hilighting the entire row which is same as other row.


    ENTIRE ROW SHOULD BE SAME.

  7. #7
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    see http://www.vbaexpress.com/forum/showthread.php?t=325

    my last post should give you enough to work from.

    Note that you need to add a reference to the Microsoft Scripting Runtime via Tools - References in the VBe

  8. #8
    sir

    ur code is highlighting only one instance of matching rows in a sheet.
    How will i highlight all the instances matching.
    And i dont want that comarison with other sheets.

    I am givin u ur code can u modify it?



    VB:
    Option Explicit 
     
    Sub HighDupes() 
        Dim ws1 As Worksheet, ws2 As Worksheet 
        Dim Row1 As Range, Row2 As Range 
        Dim Col1 As Range, Col2 As Range 
        Dim NewStr1 As String, Newstr2 As String 
         
         'Needs reference to Microsoft Scripting Runtime
        Dim MyDic As Dictionary 
         
        Set MyDic = New Dictionary 
        Set ws1 = ActiveWorkbook.Sheets(1) 
        Set ws2 = ActiveWorkbook.Sheets(2) 
         
        Application.ScreenUpdating = False 
         
        For Each Row1 In ws1.UsedRange.Rows 
             'If rows are blank then skip
            If Application.CountA(ws1.Rows(Row1.Row)) > 0 Then 
                 
                NewStr1 = "Sheet1" 
                For Each Col1 In ws1.UsedRange.Columns 
                    NewStr1 = NewStr1 & "|" & ws1.Cells(Row1.Row, Col1.Column) 
                Next 
                If MyDic.exists(NewStr1) Then 
                     'Colour intra sheet duplicates in sheet 1 as blue
                    ws1.Rows(Row1.Row).Interior.Color = vbBlue 
                Else 
                    MyDic.Add NewStr1, Row1.Row 
                End If 
            End If 
        Next 
         
         
        For Each Row2 In ws2.UsedRange.Rows 
             'If rows are blank then skip
            If Application.CountA(ws2.Rows(Row2.Row)) > 0 Then 
                Newstr2 = "Sheet1" 
                 ' to match existing keys in Sheet1
                For Each Col2 In ws2.UsedRange.Columns 
                    Newstr2 = Newstr2 & "|" & ws2.Cells(Row2.Row, Col2.Column) 
                Next 
                If MyDic.exists(Newstr2) Then 
                     'Colour inter sheet duplicates in sheet 2 as red
                    ws2.Rows(Row2.Row).Interior.Color = vbRed 
                Else 
                     'This row and the Else test is redundant really if the user isn't looking for matches
                     'within Sheet2.
                    MyDic.Add Newstr2, Row2.Row 
                End If 
            End If 
        Next 
         
        Application.ScreenUpdating = True 
         
        Set MyDic = Nothing 
        Set ws1 = Nothing 
        Set ws2 = Nothing 
    End Sub 
    
    
    Formatting tags added by mark007
    Last edited by johnske; 09-05-2005 at 01:26 AM. Reason: edited to include VBA tags

  9. #9
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    it does highlights all row matches

    I have added one new line below to highlight the first occurence of a row that has dupes. I've culled the dup work on the second sheet

    VB:
    Option Explicit 
    Sub HighDupes() 
        Dim ws1 As Worksheet 
        Dim Row1 As Range 
        Dim Col1 As Range 
        Dim NewStr1 As String 
         'Needs reference to Microsoft Scripting Runtime
        Dim MyDic As Dictionary 
        Set MyDic = New Dictionary 
        Set ws1 = ActiveWorkbook.Sheets(1) 
        Application.ScreenUpdating = False 
        For Each Row1 In ws1.UsedRange.Rows 
             'If rows are blank then skip
            If Application.CountA(ws1.Rows(Row1.Row)) > 0 Then 
                NewStr1 = "Sheet1" 
                For Each Col1 In ws1.UsedRange.Columns 
                    NewStr1 = NewStr1 & "||" & ws1.Cells(Row1.Row, Col1.Column) 
                Next 
                If MyDic.exists(NewStr1) Then 
                     'Colour intra sheet duplicates in sheet 1 as blue
                    ws1.Rows(Row1.Row).Interior.Color = vbBlue 
                    ws1.Rows(MyDic(NewStr1)).Interior.Color = vbRed 
                Else 
                    MyDic.Add NewStr1, Row1.Row 
                End If 
            End If 
        Next 
        Application.ScreenUpdating = True 
        Set MyDic = Nothing 
        Set ws1 = Nothing 
    End Sub 
    
    
    Formatting tags added by mark007

  10. #10

    Thank u sir Problem is solved

    Thank u very much sir!

    THank u sir, My problem is solved.
    Thank u for being with me.

Posting Permissions

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