Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show only filled lines (2000 r s1)

    .. into combobox1 is possible to list only a filled cell...
    in effect after the last value, in this case WEST, into combobox (GENERE) are present many blank
    note:click on button GO! sheet foglio1

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Show only filled lines (2000 r s1)

    Try
    <pre>Sub Listabox()
    ComboBox1.RowSource = "foglio2!" & _
    Range(Sheets("foglio2").[A2], Sheets("foglio2").[A2].End(xlDown)).Address
    ComboBox2.RowSource = "foglio2!B2:B200"
    ComboBox3.RowSource = "foglio2!C2:C200"
    ComboBox4.RowSource = "foglio2!D2200"
    End Sub</pre>

    Andrew C

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show only filled lines (2000 r s1)

    tks...
    And if i would want to show only unique value fro dupes?

    Similar in column A have:

    aaaaaaaaaa
    aaaaaaaaaa
    bbbbbbbbbb
    ccccccccccccc
    ccccccccccccc
    ccccccccccccc
    ccccccccccccc

    show into combobox
    aaaaaaaaaa
    bbbbbbbbbb
    ccccccccccccc

    ....

  4. #4
    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: Show only filled lines (2000 r s1)

    John Walkenbach has some demo code for Filling a ListBox With Unique Items which should be adaptable.

    Steve

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

    Re: Show only filled lines (2000 r s1)

    Put the following procedure in a module (it is based on Walkenbach's code):

    Sub FillUnique(cb As MSForms.ComboBox, AllCells As Range)
    Dim Cell As Range
    Dim NoDupes As New Collection
    Dim Item

    ' The next statement ignores the error caused
    ' by attempting to add a duplicate key to the collection.
    ' The duplicate is not added - which is just what we want!
    On Error Resume Next
    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    'Note: the 2nd argument (key) for the Add method must be a string
    Next Cell

    ' Resume normal error handling
    On Error GoTo 0

    ' Add the non-duplicated items to a ListBox
    For Each Item In NoDupes
    cb.AddItem Item
    Next Item
    End Sub

    Call it like this in the Listabox procedure:

    Sub Listabox()
    Dim ws As Worksheet
    Dim lngMaxRow As Long
    Set ws = Worksheets("Foglio2")
    lngMaxRow = ws.Range("A65536").End(xlUp).Row
    FillUnique Me.ComboBox1, ws.Range("A2:A" & lngMaxRow)
    lngMaxRow = ws.Range("B65536").End(xlUp).Row
    FillUnique Me.ComboBox2, ws.Range("B2:B" & lngMaxRow)
    lngMaxRow = ws.Range("C65536").End(xlUp).Row
    FillUnique Me.ComboBox3, ws.Range("C2:C" & lngMaxRow)
    lngMaxRow = ws.Range("D65536").End(xlUp).Row
    FillUnique Me.ComboBox4, ws.Range("D2" & lngMaxRow)
    End Sub

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show only filled lines (2000 r s1)

    TKS...

Posting Permissions

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