Show only filled lines (2000 r s1)

[sal21]

.. 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

[Andrew Cronnolly]

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

[sal21]

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

....

[sdckapr]

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

Steve

[HansV]

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

[sal21]

TKS...