Excel VBA Get unique values in one column in an Excel table

Hi

I have a table in Excel that was pulled in from SharePoint
What VBA code would I use to find the unique values in one column
as you would see if you clicked on the dropdown arrow in
the header cell?

Thanks
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Generic function to return a unique list from a range:

Public Function UniqueList(rngInput As Range) As Variant
   Dim rngCell           As Range
   Dim dic           As Object
   Dim lngRow            As Long

   Set dic = CreateObject("Scripting.Dictionary")
   
   For Each rngCell In rngInput.Cells
      If Len(rngCell.Value) <> 0 Then dic(CStr(rngCell.Value)) = Empty
    Next rngCell
   UniqueList = dic.Keys
End Function

Open in new window

0
 
ProfessorJimJamCommented:
you do not need VBA to find unique values.  use conditional formatting .

select the data then follow the navigation shown in image attached.

1.png2.png
0
 
aikimarkCommented:
You can used the AdvancedFilter feature, either in the Excel GUI or in VBA code, to populate a range with the unique values, or filter the list in-place.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you to those who avoided VBA, but I specifically asked for VBA as this is part of an automated application
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.