Named Range of Unique values

[gt213]

I have an existing named range called 'MyRange' which is dynamic. I'd like to get a new named range using vba of all unique values in 'MyRange'. I'd prefer not to have the unique values listed anywhere in my spreadsheet.

Here is the code for the named range MyRange:

With Sort
k = Application.WorksheetFunction.CountA(ws1.Rows(1).EntireRow.Cells)
Set MyRange2 = ws1.Range(ws1.Cells(1, 2), ws1.Cells(1, k))
End With

 

[Jeffrey Mahoney]

This function can be used in VBA and inside a cell. It returns a non-contiguous range of cells that are unique to the set given by Rng.

Function GetRangeOfUnique(Rng As Range) As Range
  Dim u As Range
  Dim Cel As Range
  
  For Each Cel In Rng
    If Application.CountIf(Rng, Cel.Value) = 1 Then
      If Not u Is Nothing Then
        Set u = Union(u, Cel)
      Else
        Set u = Cel
      End If
      Debug.Print Cel.Address
    End If
    
  Next Cel
  If Not u Is Nothing Then Set GetRangeOfUnique = u
  
  
End Function