Just recently discovered the scripting
dictionary which comes as part of the Windows family of operating systems, so unfortunately
this can't be levered by VBA on Macs.
Dictionaries are a common enough feature
of various languages, but something that VBA lacks natively. The closest VBA
has is collections, but these are read-only. You can add or remove items from a
collection, but you can't change an item's value. You could use an array, but
the index is always a number in an array. In a dictionary, the key (index) can
be anything you like, as long as it's unique. The value can be anything too
(even objects).
This is great for looping through lists
and finding unique values, assigning them to a key and adding the value in a
related column. You can even set the item equal to a range that you can do
something with later.
This script finds unique values in
Column A, uses Union to create a multiple range object encompassing each of the
unique values, then colours each of the ranges in a different colour.
Sub foo()
Dim c As Range, sKey As String, i As Long
With CreateObject("Scripting.Dictionary")
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
sKey = c.Value
If Not .exists(sKey) Then
Set .Item(sKey) = c.Resize(1, 2)
Else
Set .Item(sKey) = Union(.Item(sKey), c.Resize(1, 2))
End If
Next
For i = 0 To .Count - 1
Debug.Print "Values :" & .Keys()(i) & ", with Range(" & _
.Items()(i).Address & ") has " & .Items()(i).Cells.Count & " cells"
.Items()(i).Interior.ColorIndex = 34 + i
Next i
End With
End Sub
