locked
Highlight the excel data before delete RRS feed

  • Question

  • Hello Developers,

    I have 3 combo box on user form where user select the column name and based on that, duplicate data gets deleted.

    Below is the code. I want before duplicate data gets deleted, duplicate data shall be highlited in green colur.

    Dim strrow1 As String
    Dim strrow2 As String
    Dim strrow3 As String
    strrow1 = cbxField1.Value
    strrow2 = cbxField2.Value
    strrow3 = cbxField3.Value
     If strrow1 = "" Then
     
     ActiveSheet.Range("$A$1:$N$100").RemoveDuplicates _
     Columns:=Array(Application.match(strrow2, ActiveSheet.Range("1:1"), False), _
     Application.match(strrow3, ActiveSheet.Range("1:1"), False)), _
      Header:=xlYes
     End If
     
     If strrow2 = "" Then
     
     ActiveSheet.Range("$A$1:$Z$10000").RemoveDuplicates _
     Columns:=Array(Application.match(strrow1, ActiveSheet.Range("1:1"), False), _
     Application.match(strrow3, ActiveSheet.Range("1:1"), False)), _
      Header:=xlYes
     End If
     
     If strrow3 = "" Then
     
     ActiveSheet.Range("$A$1:$N$100").RemoveDuplicates _
     Columns:=Array(Application.match(strrow1, ActiveSheet.Range("1:1"), False), _
     Application.match(strrow2, ActiveSheet.Range("1:1"), False)), _
      Header:=xlYes
    Else
     
    ActiveSheet.Range("$A$1:$N$100").RemoveDuplicates _
     Columns:=Array(Application.match(strrow1, ActiveSheet.Range("1:1"), False), _
     Application.match(strrow2, ActiveSheet.Range("1:1"), False), _
     Application.match(strrow3, ActiveSheet.Range("1:1"), False)), Header:=xlYes
     End If


    • Edited by zaveri cc Tuesday, December 24, 2013 4:37 PM
    Tuesday, December 24, 2013 4:37 PM

Answers

  • Hi zaveri,

    According to your description, you want to high light the duplicate data before delete. We can use condition formatting to achieve this goal. I record a macro and modify a little for your reference:

    Sub HighLightDubplicate(targetRange As Range)
    
    
        targetRange.FormatConditions.AddUniqueValues
    
      
    
        targetRange.FormatConditions(targetRange.FormatConditions.Count).SetFirstPriority
    
        targetRange.FormatConditions(targetRange.FormatConditions.Count).DupeUnique = xlDuplicate
    
    
        With targetRange.FormatConditions(targetRange.FormatConditions.Count).Interior
    
            .PatternColorIndex = xlAutomatic
    
            .Color = 13561798
    
            .TintAndShade = 0
    
        End With
    
        targetRange.FormatConditions(targetRange.FormatConditions.Count).StopIfTrue = False
    
    End Sub

    However, when I put this code before the code deleting, it is too quick to see the data which is colored. So I suggest you run this code before you delete the duplicate data separately. And you can clear the formatting by using code below:

    Sub ClearFormats(targetRange As Range)
    
        targetRange.ClearFormats
    
    End Sub

    You can get more detail about condition formatting from article below:

    Find duplicate values fast by applying conditional formatting

    FormatConditions.AddUniqueValues

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, December 25, 2013 9:55 AM
  • Hi zaveri,

    Before you use the variable, you need to initialize it. For example, if you want to highlight the duplicate cell in range("$A$1:$A$100"). You can refer to code below:

    Private Sub CommandButton1_Click()
    Sheet1.Select
    Dim targetrange As range
    set targetrange =Range("A1:A100") 'init the variable
        targetrange.FormatConditions.AddUniqueValues' this line turns yellow
      
        targetrange.FormatConditions(targetrange.FormatConditions.Count).SetFirstPriority
        targetrange.FormatConditions(targetrange.FormatConditions.Count).DupeUnique = xlDuplicate
        With targetrange.FormatConditions(targetrange.FormatConditions.Count).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13561798
            .TintAndShade = 0
        End With
        targetrange.FormatConditions(targetrange.FormatConditions.Count).StopIfTrue = False
    End Sub
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 26, 2013 2:17 AM

All replies

  • Hi zaveri,

    According to your description, you want to high light the duplicate data before delete. We can use condition formatting to achieve this goal. I record a macro and modify a little for your reference:

    Sub HighLightDubplicate(targetRange As Range)
    
    
        targetRange.FormatConditions.AddUniqueValues
    
      
    
        targetRange.FormatConditions(targetRange.FormatConditions.Count).SetFirstPriority
    
        targetRange.FormatConditions(targetRange.FormatConditions.Count).DupeUnique = xlDuplicate
    
    
        With targetRange.FormatConditions(targetRange.FormatConditions.Count).Interior
    
            .PatternColorIndex = xlAutomatic
    
            .Color = 13561798
    
            .TintAndShade = 0
    
        End With
    
        targetRange.FormatConditions(targetRange.FormatConditions.Count).StopIfTrue = False
    
    End Sub

    However, when I put this code before the code deleting, it is too quick to see the data which is colored. So I suggest you run this code before you delete the duplicate data separately. And you can clear the formatting by using code below:

    Sub ClearFormats(targetRange As Range)
    
        targetRange.ClearFormats
    
    End Sub

    You can get more detail about condition formatting from article below:

    Find duplicate values fast by applying conditional formatting

    FormatConditions.AddUniqueValues

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, December 25, 2013 9:55 AM
  • The Highlight duplicate reocrd code throws error- Run time error 91- Object variable or with block variable not set.

    Private Sub CommandButton1_Click() Sheet1.Select Dim targetrange As range targetrange.FormatConditions.AddUniqueValues' this line

    turns yellow targetrange.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority targetrange.FormatConditions(1).DupeUnique = xlDuplicate With targetrange.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13561798 .TintAndShade = 0 End With targetrange.FormatConditions(1).StopIfTrue = False End Sub



    • Edited by zaveri cc Wednesday, December 25, 2013 4:20 PM
    Wednesday, December 25, 2013 4:18 PM
  • Hi zaveri,

    Before you use the variable, you need to initialize it. For example, if you want to highlight the duplicate cell in range("$A$1:$A$100"). You can refer to code below:

    Private Sub CommandButton1_Click()
    Sheet1.Select
    Dim targetrange As range
    set targetrange =Range("A1:A100") 'init the variable
        targetrange.FormatConditions.AddUniqueValues' this line turns yellow
      
        targetrange.FormatConditions(targetrange.FormatConditions.Count).SetFirstPriority
        targetrange.FormatConditions(targetrange.FormatConditions.Count).DupeUnique = xlDuplicate
        With targetrange.FormatConditions(targetrange.FormatConditions.Count).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13561798
            .TintAndShade = 0
        End With
        targetrange.FormatConditions(targetrange.FormatConditions.Count).StopIfTrue = False
    End Sub
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 26, 2013 2:17 AM
  • Thanks this works.

    Is it possible to show msg box for the number of duplicate records that are deleted. Below is the code

    Dim strrow1 As String
    Dim strrow2 As String
    Dim strrow3 As String
    strrow1 = cbxField1.Value
    strrow2 = cbxField2.Value
    strrow3 = cbxField3.Value
     
     If strrow2 = "" Then
     
     ActiveSheet.range("$A$1:$Z$10000").RemoveDuplicates _
     Columns:=Array(Application.match(strrow1, ActiveSheet.range("1:1"), False))
     
     End If
     
     If strrow3 = "" Then
     
     ActiveSheet.range("$A$1:$N$100").RemoveDuplicates _
     Columns:=Array(Application.match(strrow1, ActiveSheet.range("1:1"), False), _
     Application.match(strrow2, ActiveSheet.range("1:1"), False)), _
      Header:=xlYes
    Else
     
    ActiveSheet.range("$A$1:$N$100").RemoveDuplicates _
     Columns:=Array(Application.match(strrow1, ActiveSheet.range("1:1"), False), _
     Application.match(strrow2, ActiveSheet.range("1:1"), False), _
     Application.match(strrow3, ActiveSheet.range("1:1"), False)), Header:=xlYes
     End If

    • Edited by zaveri cc Thursday, December 26, 2013 3:47 PM
    Thursday, December 26, 2013 3:46 PM
  • Hi zaveri,

    I found an article about geting the count of unique values among duplicates:

    http://office.microsoft.com/en-in/excel-help/find-duplicate-values-fast-by-applying-conditional-formatting-HA102809491.aspx

    To achieve the goal, you can get the total count of the range and use it to minus the count of unique values.

    Formula below may be helpful:

    FREQUENCY

    In addtion, since the question is not realte to the orignal post, if this is not helpful, I suggest you repost the question to let more partners join this discusstion and share their their knowledge.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 31, 2013 1:41 AM