Solved: LOAD only visible cells to combobox in userform

[danovkos]

Hi all,
i use this command for loading list of names to my combobox in my userform.


COMBOkries.list = Worksheets("Krieš").Range("k2:k87").Value


Now i need load only visible cells (data) to my combo. I use in sheet "krieš" filter.

Is it possible to use somthing like

COMBOkries.list = Worksheets("Krieš").Range("k2:k87").Value.visible

but of course this doenst works :(

For filtering i use textbox in sheet krieš with this code


Private Sub TB_Kries_ZMENY_Change()
Dim LastRow As Long

LastRow = Range("K65535").End(xlUp).Row

If TB_Kries_ZMENY.Text <> "" Then
ActiveSheet.Range("$K$2:$K$" & LastRow).AutoFilter Field:=11, _
Criteria1:="=*" & TB_Kries_ZMENY.Text & "*" _
, Operator:=xlOr
Else
ActiveSheet.Range("$K$2:$K$" & LastRow).AutoFilter Field:=11
End If
End Sub


Or maybe is it easier implement it direct to userform...but how? :dunno

thx for help

[Bob Phillips]

You would have to iterate all of the visible cells and add them individually.

[danovkos]

It seems to complicatet for me.
But thx for your advise.

[JKwan]

Try this out

Sub loadcombo()
Dim rRange As Range
Dim VisibleRange As Range

Set rRange = Sheet1.Range("A1:A20")

Set VisibleRange = rRange.SpecialCells(xlCellTypeVisible)
Sheet1.ComboBox1.Clear
For Each rcell In VisibleRange
Sheet1.ComboBox1.AddItem rcell
Next
End Sub

[danovkos]

thx JKwan.
this code helps me a lot

:thumb :thumb
thank you very much