combobox

[jim c]

i have a combobox bound to a named range. i have a
command button that runs the autofilter for the list the
combobox is bound to. after i filter the list i would
like to have combobox just display the filterd list. the
list is dynamic so i wrote a macro to resize list.

Public Sub List()
Dim test As Range
Sheets("sheet1").Activate
Sheets("sheet1").Range("a2").Select
Set test = ActiveCell.CurrentRegion
heads = test.ListHeaderRows
If heads > 0 Then
Set test = test.Resize(test.Rows.Count - heads)
Set test = test.Offset(heads)
Sheets("sheet1").Range("a2").Select
test.Name = "test"
Sheets("sheet1").Columns.AutoFit
Set test = test.SpecialCells(xlCellTypeVisible)
End If

End Sub



i am trying to use this line of code to size the list to
just the visible rows... but of course it doesnt work...

Set test = test.SpecialCells(xlCellTypeVisible)

any suggestions?

 

[Dave Peterson]

I think you'll want to cycle through those visible cells. I didn't see any code
for applying the filter and I assumed that the combobox was on the worksheet,
but this might get you started:

Option Explicit

Public Sub List()
Dim test As Range
Dim heads As Long
Dim myCell As Range

Sheets("sheet1").Activate
Sheets("sheet1").Range("a2").Select
Set test = ActiveCell.CurrentRegion
heads = test.ListHeaderRows
If heads > 0 Then
Set test = test.Resize(test.Rows.Count - heads)
Set test = test.Offset(heads)
Sheets("sheet1").Range("a2").Select
test.Name = "test"
Sheets("sheet1").Columns.AutoFit

On Error Resume Next
Set test = test.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If test Is Nothing Then
'shouldn't happen?
Else
Worksheets("sheet1").ComboBox1.Clear
For Each myCell In test.Cells
Worksheets("sheet1").ComboBox1.AddItem myCell.Value
Next myCell
End If

End If

End Sub