Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dropdown Elements (03)

    When you have filters applied one can see a unique list of items in a column. What I would like to do by vba code is to select each item in turn so that I would be able to print the filtered data. The list of items is dynamic ie constantly changing so to hard code each one individually would not work.

    Any suggestions would be appreciated,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Elements (03)

    The code below will create an array named strUVals with the unique values in column H. Is that what you want?

    <code>
    Public Sub GetUnique()
    Dim strUVals() As String
    Dim I As Long, J As Long, K As Long, lLast As Long
    lLast = Range("H65536").End(xlUp).Row - 1
    K = 0
    For I = 0 To lLast
    For J = 1 To K
    If Range("H1").Offset(I, 0).Value = strUVals(J) Then Exit For
    Next J
    If J > K Then
    K = K + 1
    ReDim Preserve strUVals(1 To K)
    strUVals(K) = Range("H1").Offset(I, 0).Value
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dropdown Elements (03)

    The Unique list from filtering is not available in VB (I think the routine creates it on the fly).

    The easisest way may be to create the list at runtime. John Walkenbach's code at Filling a ListBox With Unique Items could be adapted to get the list and go thru them in code (rather than filling the listbox).

    An alternate way would be to use advanced filter (or a pivot table) to create the unique list on a temp sheet and then just work thru this temp list to get each unique item.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dropdown Elements (03)

    See the thread starting at <post:=320,628>post 320,628</post:>, posted by one jstevens.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dropdown Elements (03)

    Legare,

    Your suggestion worked quite nicely. Thank you.

    Hans -
    I completely forgot about that post. My search did not return that particular thread.

    Regards,
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •