add items in combo box only filtered items.

[mahaveer]

suppose i have 15 items in Column A of sheet1.
now i filtered A column on some criteria and 7 items are showing on sheet 1 column A.

now i want to add only filtered (7) items on combobox 1 but how?


Thanks in advance

Regards
CA Mahaveer Somani

 

[Abhijeet R. Joshi]

You can use the below code:

Option Explicit
Sub RemoveDuplicates()
  Dim AllCells As Range, Cell As Range
  Dim NoDupes As New Collection
  Dim i As Integer, j As Integer
  Dim Swap1, Swap2, Item
  Range("A1:A15").Select
  Set AllCells = Selection.SpecialCells(xlCellTypeVisible)
  On Error Resume Next
  For Each Cell In AllCells
  NoDupes.Add Cell.Value, CStr(Cell.Value)
  Next Cell
  On Error GoTo 0
  For i = 1 To NoDupes.Count - 1
  For j = i + 1 To NoDupes.Count
  If NoDupes(i) > NoDupes(j) Then
  Swap1 = NoDupes(i)
  Swap2 = NoDupes(j)
  NoDupes.Add Swap1, before:=j
  NoDupes.Add Swap2, before:=i
  NoDupes.Remove i + 1
  NoDupes.Remove j + 1
  End If
  Next j
  Next i
  For Each Item In NoDupes
  UserForm1.ComboBox1.AddItem Item
  Next Item
  UserForm1.Show
End Sub

Referred from:http://j-walk.com/ss/excel/tips/tip47.htm