CODEWITHSUNDEEP
arraysvbaexcel
Jonas
Jonas

Reputation: 1234

Collect all names in a column and put them in an Array in Excel

I have an excel worksheet with a table of data. One column of the table contains the names of companies. For example, "Apple", "Microsoft", "Asus". The column may contain duplicate company names.

How can I populate an array in VBA which contains the distinct members of this column?

Upvotes: 0

Views: 5292

Answers (1)

JMax
JMax

Reputation: 26601

You can use a vba collection which does not allow duplicates for the same key:

Option Explicit

Sub UniqueList()
  Dim i As Long
  Dim rList As Range
  Dim cUnique As New Collection
  Dim aFinal() As String

  'change the range depending on the size of your title (or use a named range)
  Set rList = Range("A1:M1")

  'Loop over every column and add the value to the collection (with unique key)
  For i = 1 To rList.Columns.Count
      On Error Resume Next
      cUnique.Add rList(1, i), CStr(rList(1, i))
  Next i

  'Store back the value from the collection to an array
  ReDim aFinal(1 To cUnique.Count, 1 To 1)
  For i = 1 To cUnique.Count
      aFinal(i, 1) = cUnique(i)
  Next i

  'Use aFinal to do whatever you want
End Sub

Upvotes: 3

Related Questions