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?



    [VBA] 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
    [/VBA]
    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

    [vba]
    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
    [/vba]

  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
  •