How do I retrieve the column values listed when clicking the autofilter field
arrow?

I want to display these values in a msgbox.

Thanks for any suggestion.

Hi Frank,

I'm not sure if the autofilter or filter object could help you, bu
what you actually want is the unique values in a column. Below Su
would give you these (in this case you would have selected the colum
on which you want to filter, but you can use of course whatever rang
you want):

Sub effe()
Dim cel As Range, col As Collection
Dim i As Integer

Set col = New Collection

On Error Resume Next
For Each cel In Selection
col.Add Str(cel.Value), Str(cel.Value)
Next cel

For i = 0 To col.Count - 1
MsgBox col(i)
Next i

Set col = Nothing
End Sub

HTH,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=65575
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph

Thanks for the solution. It works just as intended, but it would be nice to
be able to present a list of the cases in the same msgbox, instead of one
msgbox pop-up for each case.

Is this possible in an easy way?


Frank

"TonT" wrote:


Hi Frank,

I'm not sure if the autofilter or filter object could help you, but
what you actually want is the unique values in a column. Below Sub
would give you these (in this case you would have selected the column
on which you want to filter, but you can use of course whatever range
you want):

Sub effe()
Dim cel As Range, col As Collection
Dim i As Integer

Set col = New Collection

On Error Resume Next
For Each cel In Selection
col.Add Str(cel.Value), Str(cel.Value)
Next cel

For i = 0 To col.Count - 1
MsgBox col(i)
Next i

Set col = Nothing
End Sub

HTH,
Ton Teuns


--
TonT
------------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=47
View this thread: http://www.officehelp.in/showthread.php?t=655750
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.php

Hi Frank,

Easiest way is to concatenate the values into one string and t
separate them by so-called carriage returns (as with ye old typewriter
goto beginning of new line).
I forgot to tell that this method makes use of the fact that
collection holds only unique values when you add items with a specifie
key. Not my invention, I may have read it in an earlier post.


Sub effe()
Dim rCel As Range, clFilter As Collection
Dim iCntr As Integer
Dim sMsg As String

' only unique items are allowed
' when items are added with a key
Set clFilter = New Collection

' make sure duplicate keys don't harm us
On Error Resume Next
For Each rCel In Selection
' try to add the value
' if it is already there the error handler
' will take us to the next one
clFilter.Add str(rCel.Value), str(rCel.Value)
Next rCel

For iCntr = 0 To clFilter.Count - 1
' put all the values in the message string
' separated by carriage returns
sMsg = sMsg & clFilter(iCntr) & vbCr
Next iCntr

' now display the message
MsgBox sMsg, vbInformation, "Unique values"

Set clFilter = Nothing
End Sub

Regards,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=65575
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph

Thank you for the suggestion. A perfect solution to my problem :-)

Frank

"TonT" wrote:


Hi Frank,

Easiest way is to concatenate the values into one string and to
separate them by so-called carriage returns (as with ye old typewriter,
goto beginning of new line).
I forgot to tell that this method makes use of the fact that a
collection holds only unique values when you add items with a specified
key. Not my invention, I may have read it in an earlier post.


Sub effe()
Dim rCel As Range, clFilter As Collection
Dim iCntr As Integer
Dim sMsg As String

' only unique items are allowed
' when items are added with a key
Set clFilter = New Collection

' make sure duplicate keys don't harm us
On Error Resume Next
For Each rCel In Selection
' try to add the value
' if it is already there the error handler
' will take us to the next one
clFilter.Add str(rCel.Value), str(rCel.Value)
Next rCel

For iCntr = 0 To clFilter.Count - 1
' put all the values in the message string
' separated by carriage returns
sMsg = sMsg & clFilter(iCntr) & vbCr
Next iCntr

' now display the message
MsgBox sMsg, vbInformation, "Unique values"

Set clFilter = Nothing
End Sub

Regards,
Ton Teuns


--
TonT
------------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=47
View this thread: http://www.officehelp.in/showthread.php?t=655750
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.php