Option
Explicit
Private
Sub
ComboBox1_DropButtonClick()
Dim
objRange
As
Range
Dim
avntArray()
As
Variant
Dim
lngRow
As
Long
, lngAreaCount
As
Long
, lngColumnsCount
As
Long
Dim
ialngCount
As
Long
, ialngColumn
As
Long
ComboBox1.ListFillRange = vbNullString
Set
objRange = AutoFilter.Range.SpecialCells(Type:=xlCellTypeVisible)
With
objRange
lngColumnsCount = .Columns.Count
For
lngAreaCount = 1
To
.Areas.Count
With
.Areas(lngAreaCount)
For
lngRow = 1
To
.Rows.Count
ReDim
Preserve
avntArray(lngColumnsCount - 1, ialngCount)
As
Variant
For
ialngColumn = 1
To
lngColumnsCount
avntArray(ialngColumn - 1, ialngCount) = .Cells(lngRow, ialngColumn)
Next
ialngCount = ialngCount + 1
Next
End
With
Next
End
With
ComboBox1.Column() = avntArray()
Set
objRange =
Nothing
End
Sub