RE: detemining values in a range

Here is some code... It looks at the currently selected cells and creates a
new *** that contains only the unique items frm that selection...

Public Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Object 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Work*** 'Work*** to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

Application.ScreenUpdating = False
'Create range to be searched
Set rngToSearch = Intersect(Active***.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = CreateObject("Scripting.Dictionary")

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value <> Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new ***
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"mwam423" wrote:

hi jim, yes, that's exactly what i want, listing of unique values

"Jim Thomlinson" wrote:

What did you want to know... Are you looking for a listing of the unique
entries or ???

.