View Full Version : Solved: ListBox1.RowSource for visible Rows.
omnibuster
07-17-2012, 12:21 PM
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
07-17-2012, 01:08 PM
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
07-17-2012, 01:55 PM
CodeNinja.
Thanks but no thanks.
I was thinking visible/hidden as parameter.
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
07-19-2012, 01:16 PM
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
Avoid using 'additem' to populate a combobox/listbox
omnibuster
07-20-2012, 05:22 AM
snb.
And your suggestions are....
The same as I posted before....
omnibuster
07-20-2012, 07:19 AM
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)
omnibuster
07-20-2012, 07:57 AM
Thanks snb.
Case close.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.