VBA question - autofilter combobox
 

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

--
Message posted from http://www.ExcelForum.com

VBA question - autofilter combobox
 

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)

"ajliaks " wrote in message
...
Hi!

I have userform called "Exporting", which includes combobox called
"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.


---
Message posted from http://www.ExcelForum.com/

VBA question - autofilter combobox
 

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.
--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"ajliaks " wrote in message
...
Hi!

I have userform called "Exporting", which includes combobox called
"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.


---
Message posted from http://www.ExcelForum.com/