Get the list from a table headers drop down

[nemmi69]

Instead of reinventing the wheel is there a way to get the unique list that shows in a headers drop down?

 

[nemmi69]

because i didn't copy the entire code as thats not allowed

 

[Peter_SSs]

because i didn't copy the entire code as thats not allowed

Fair enough, but all we have to test with and comment on is what you actually give us - & we are trying to help.

BTW, did you try the one-liner?

 

[nemmi69]

I tried the 1 liner but it didnt want to play

 

[Peter_SSs]

I tried the 1 liner but it didnt want to play

"didn't want to play" is very vague

Did it give an error message?
Return wrong values?
Do nothing?
Crash excel?
Something else?

Here is my sample table

nemmi69.xlsm
	A	B	C	D
1
2		Unit	Action
3		2	Initial
4		4	Replacement
5		3	Initial
6		4	Replacement
7		1	Repair
8
Sheet1

And my code

Sub Test()
  Dim TblId As ListObject
  Dim TblHdr As String
  
  Set TblId = ActiveSheet.ListObjects(1)
  Dim LArr As Variant
  TblHdr = "Action"
  LArr = Evaluate("sort(unique(" & TblId.Name & "[" & TblHdr & "]))")
End Sub

And the result held in LArr. In what way is that not what you are after?

 

[nemmi69]

Ah, I am trying to gather the unique elements into an array for use with a report macro

 

[Peter_SSs]

Ah, I am trying to gather the unique elements into an array for use with a report macro

.. which is exactly what my code does, isn't it?

.. or is it that my array is a 2-D array whereas yours was a 1-D array? If that was the issue then try this modification.

Sub Test()
  Dim TblId As ListObject
  Dim TblHdr As String
  Dim LArr As Variant
  
  Set TblId = ActiveSheet.ListObjects(1)  '<- Set to your particular ListObject/Table
  TblHdr = "Action"                       '<- Set to your particular table heading
  LArr = Application.Transpose(Evaluate("sort(unique(" & TblId.Name & "[" & TblHdr & "]))"))
End Sub

 

[nemmi69]

Sorry for late response. Busy at work. Yes @Peter_SSs your method works . Thanks!

 

[Peter_SSs]

Yes @Peter_SSs your method works . Thanks!

You're welcome. Thanks for the confirmation.