Solved: ListBox1.RowSource for visible Rows.

[omnibuster]

This my code.


ListBox1.RowSource = Sheet1.Range("A2",Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(0,3)).Address(External:=True)


Is it possible modify this code for only visible rows?

[CodeNinja]

Might not be the most efficient way, but you could always do the following:


dim i as integer
for i = 1 to sheet1.range("A" & rows.count).end(xlup).row
if sheet1.range("A" & i).entirerow.hidden = false then
listbox1.additem blah
end if
next i

[omnibuster]

CodeNinja.
Thanks but no thanks.

I was thinking visible/hidden as parameter.

[snb]

Avoid Rowsource to populate a listbox/combobox. Use .list instead


sub snb()
for each cl in columns(1).specialcells(2).offset(1).specialcells(2).offset(,3).specialcell s(12)
c01=c01 & "|" & cl.value
next
listbox1.list=split(mid(c01,2),"|")
end sub

[omnibuster]

Thanks snb.

specialcells(12)


I made that way.


Private Sub CommandButton_Click()
Dim cl As Range
Dim rng As Range
Set rng =Sheet2.Range("A2", Sheet2.Range("A" &Rows.Count).End(xlUp))
For Each cl In rng.SpecialCells(12)
With Me
With ListBox1
.AddItem cl
.Column(1,.ListCount - 1) = cl.Offset(, 1)
.Column(2,.ListCount - 1) = cl.Offset(, 2)
End With
End With
Next cl
Set rng = Nothing
End Sub

[snb]

Avoid using 'additem' to populate a combobox/listbox

[omnibuster]

snb.
And your suggestions are....

[snb]

The same as I posted before....

[omnibuster]

snb.
Sorry.
Yesterday I spent hours of your code and ...nothing.

Find the difference of at least 3. :)


http://www.upload.ee/thumb/2529409/pilt1.JPG (http://www.upload.ee/image/2529409/pilt1.JPG)

http://www.upload.ee/thumb/2529414/Pilt2.JPG (http://www.upload.ee/image/2529414/Pilt2.JPG)

[snb]

Even simpler:

[omnibuster]

Thanks snb.
Case close.