Case Sensitive removal of duplicates in VBA

[awsumchillicrab]

Hi all,

In removing duplicates with case-sensitivity, I can't use a COUNTIF so the substitute for counting is this formula entered with Ctrl-Shift-Enter:

=SUMPRODUCT(--EXACT($D$13:$D$16,D15))

Now I want to incorporate this into VBA, so that I can include the .delete method. How do I do it?

For starters, VBA doesn't seem to have WorksheetFunction.Exact

 

[Weaver]

try this:

Option Compare Binary
Sub deleteExactDuplicates(ByVal rng As Range)
    Application.ScreenUpdating = False
    With CreateObject("scripting.dictionary")
        For Each i In rng.Cells
            v = i.Value
            If .exists(v) Then
                i.ClearContents
            Else
                .Add v, 1
            End If
        Next i
    End With
    On Error Resume Next
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

call the subroutine in your code by passing the range to be examined:

Sub test()
   deleteExactDuplicates Range("A1:A10")
End Sub

 

[pgc01]

Hi

Just a small remark:

An alternative to the

Option Compare Binary

that sets the comparison mode to the whole module, would be to just use in the dictionary object:

    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbBinaryCompare

 

[Weaver]

Hi

Just a small remark:

An alternative to the

Option Compare Binary

that sets the comparison mode to the whole module, would be to just use in the dictionary object:

    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbBinaryCompare

Nice - didn't know you could do that. Prefer it to the 'whole module' setting.

 

[HESH]

try this:

Option Compare Binary
Sub deleteExactDuplicates(ByVal rng As Range)
    Application.ScreenUpdating = False
    With CreateObject("scripting.dictionary")
        For Each i In rng.Cells
            v = i.Value
            If .exists(v) Then
                i.ClearContents
            Else
                .Add v, 1
            End If
        Next i
    End With
    On Error Resume Next
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

call the subroutine in your code by passing the range to be examined:

Sub test()
   deleteExactDuplicates Range("A1:A10")
End Sub

for learning:

Can you please explain the code

 

[Weaver]

Hi

Just a small remark:

An alternative to the

Option Compare Binary

that sets the comparison mode to the whole module, would be to just use in the dictionary object:

    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbBinaryCompare

Turns out the dictionary object is case sensitive by default. I'd not noticed this before.
http://www.experts-exchange.com/Sof...A_3391-Using-the-Dictionary-Class-in-VBA.html

 

[pgc01]

Turns out the dictionary object is case sensitive by default. I'd not noticed this before.

Hi Weaver

Thank you. You are right. Maybe I should learn to think before I post.

I say the Option statement at the beginning of the module and since I usually use the .CompareMode property of the dictionary object I didn't even stop to think if this was needed here (not the first time it happens ).

On the other hand, for the not-case-sensitive comparison, the .CompareMode property is handy:

http://www.mrexcel.com/forum/showthread.php?t=249483#7

 

[Weaver]

PGC - I wasn't suggesting your input was invalid, in fact, it was because of it that I did some research, in case there was anything else that I was missing. Turns out there wasn't, but worth a look anyway. There's a good link in another thread that Hech started on this subject.

 

[awsumchillicrab]

Yikes, this is some advanced stuff!

I'd have to take some time to figure this out.
I'm guessing there's no way to use the Excel formula

=SUMPRODUCT(--EXACT($D$13:$D$16,D15))

in VBA with .delete?

 

[Weaver]

Yikes, this is some advanced stuff!

I'd have to take some time to figure this out.
I'm guessing there's no way to use the Excel formula



in VBA with .delete?

There probably is, it's just that given the scope of your question, it's not the way I'd choose to go. I hope this solution works for you.