Populate a Multicolumn ComboBox with filtered range
You need to have a criteria range that specifies to copy records with
Business Type 1.
To do the 4 columns
Sub Populate_Combobox()
Dim rnData As Range
Dim vaData As Variant
Dim i As Long
Dim CritRng as Range
With Sheet1
' Set up the criteria range
Set crtRng = .Range("F1:F2")
.Range("F1").Value = .Range("B2").Value
.Range("F2").Value = 1
Set rnData = .Range(.Range("A1"), .Range("A65536").End(xlUp)).Resize(,4)
' Put headers in row1
.Range("BA1").Resize(1,4).Value = .Range("A1").Resize(1,4).value
rnData.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=crtRng, _
CopyToRange:=.Range("BA1").Resize(1,4), _
Unique:=False
vaData = .Range(.Range("BA2"),
..Range("BA65536").End(xlUp)).Resize(,4).Value
.Range(.Range("BA1"), .Range("BA65536").End(xlUp)).ClearContents
End With
With ComboBox1
.Clear
.ColumnCount = 4
.List = vaData
.ListIndex = -1
End With
' clear out the criteria range
crtRng.clearcontents
End Sub
--
Regards,
Tom Ogilvy
" wrote:
I have a worksheet with 40 columns.
This is part of the worksheet:
-- A
B C
D .... BA
1 ID
BussinessType Name
City 1
2 123451
1 John Lennon New Orleans
3 123452
2 Luigi Federline Ontario
4 123453
1 Arthur Diaz Los Angeles
5 123454
2 Jane Lisboa Portland
I want to populate a 4-column combobox (within these columns) with
rows where BussinessType is equal to 1.
This is my Code:
'----Start Code----
Sub Populate_Combobox()
Dim rnData As Range
Dim vaData As Variant
Dim i As Long
With Sheet1
Set rnData = .Range(.Range("B1"), .Range("B65536").End(xlUp))
rnData.AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("BA1"), Unique:=True
vaData = .Range(.Range("BA2"), .Range("BA65536").End(xlUp)).Value
.Range(.Range("BA1"), .Range("BA65536").End(xlUp)).ClearContents
End With
With ComboBox1
.Clear
.List = vaData
.ListIndex = -1
End With
End Sub
'----End Code----
But this only works in a single column combobox, and still gives me
all the records.
What is the problem?
Juan
|