VBA question - autofilter combobox

A

ajliaks

Hi!

I have userform called "Exporting", which includes combobox calle
"ChapExpCB"

Now ChapExpCB shows me a list of items included in a selected column
Some of the items are repetead, and I need to Show each one just once
Exacly like autofilter does.

How can I do?

Thanks
 
B

Bob Phillips

Here is one way

Dim iLastRow As Long
Dim i As Long
Dim oWS As Worksheet
Dim xrow
Set oWS = Worksheets("Sheet2")
iLastRow = oWS.Cells(Rows.Count, "A").End(xlUp).row
oWS.Columns(2).Insert
oWS.Cells(1, "B").Formula = "=COUNTIF($A$1:A1,A1)"
oWS.Cells(1, "B").AutoFill Destination:=oWS.Cells(1, "B"). _
Resize(iLastRow, 1)
oWS.Range("B1").EntireRow.Insert
oWS.Columns("B:B").AutoFilter Field:=1, Criteria1:="=1"
With ChapExpCB
.Clear
For Each xrow In oWS.Cells.SpecialCells(xlCellTypeVisible).Rows
If xrow.row > iLastRow + 1 Then
Exit For
End If
If xrow.row <> 1 Then
.AddItem oWS.Cells(xrow.row, "A").Value
End If
Next xrow
.ListIndex = 0
End With
oWS.Columns(2).Delete
oWS.Rows(1).Delete



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
P

Patrick Molloy

another way would be to populate the combo in the form's initialise event
using a scripting runtime dictionary to filter out duplicates....
'''set a reference to MS Scripting runtime...

Private Sub UserForm_Initialize()
Dim dic As New Scripting.Dictionary
Dim rSource As Range, cell As Range
Dim sVal As String

' point to your column
Set rSource = Range("MyList")

With ComboBox1
For Each cell In rSource.Cells
sVal = cell.Value
If Not dic.Exists(sVal) Then
dic.Add sVal, sVal
.AddItem sVal
End If
Next
End With
Set dic = Nothing
End Sub

if the item is in the dictionary, we have it so skip on to the next item. If
an item isn't in the dictionary, then add it to the dictionary and to the
combobox. The dictionary object is extremely useful, Its effectively a
collection, but unlike a collection, with a dictianry you can test teh
existence of a key...you can also extract the keys and loop through them.
 
Top