# Combo Box - Unique Values and Sort.

#### LEXCERM

##### Active Member
Guys/Gals,

I have a combo box on a form with it's row source pointing to column 'd' of 'sheet1'.

How can I get the combo to:-
a) show unique values only (and not all the duplicate values)
b) sort the results in the combo in ascending order

PAUL.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Thanks GETTINGBETTER,

That is a good resolution to the sort issue, I now just need to get rid of the duplicate records.

Cheers,
PAUL.

I appreciate Gettingbetter linking to one of my posts, which spoke to the issue on that thread of sorting the ComboBox items. The below code, which goes in the userform module, will populate only unique values from column D as you asked, and then sort them in ascending order. Delete the Initialization event code you currently have and replace it with the following code. Remember to clear the RowSource property of your ComboBox first, if you have not already done so. If by chance column D contains formulas, post back...my code assumed it only contains constants.

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Dim Coll As Collection, cell As Range
Dim unsorted As Boolean, i As Integer, temp As Variant
On Error Resume Next
Set Coll = New Collection
With ComboBox1
.Clear
For Each cell In Worksheets("Sheet1").Columns(4).SpecialCells(2)
If Len(cell) <> 0 Then
Err.Clear
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
unsorted = True
Do
unsorted = False
For i = 0 To UBound(.List) - 1
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
unsorted = True
Exit For
End If
Next i
Loop While unsorted = True
.ListIndex = 0 'optional
End With
Set Coll = Nothing
Application.ScreenUpdating = True
End Sub

Thanks Tom,

That's excellent! Only two minor snags:-

1) How do I change the coding so that the first cell is not included (i.e. D1) as this is the header.
2) This works well with alpha characters, buT when I try numerics or dates it doesn't populate the combo. This might be something similar to what you said regarding formulas?

Much thanks,
PAUL.

This should handle those snags:

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Dim Coll As Collection, cell As Range, LR As Long
Dim unsorted As Boolean, i As Integer, temp As Variant
Dim SourceSheet As Worksheet
Set SourceSheet = Worksheets("Sheet1")
LR = SourceSheet.Cells(Rows.Count, 4).End(xlUp).row
On Error Resume Next
Set Coll = New Collection
With ComboBox1
.Clear
For Each cell In SourceSheet.Range("D2:D" & LR)
If Len(cell) <> 0 Then
Err.Clear
If Err.Number = 0 Then .AddItem cell.Text
End If
Next cell
unsorted = True
Do
unsorted = False
For i = 0 To UBound(.List) - 1
If .List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
unsorted = True
Exit For
End If
Next i
Loop While unsorted = True
.ListIndex = 0 'optional
End With
Set Coll = Nothing
Set SourceSheet = Nothing
Application.ScreenUpdating = True
End Sub

Tom, thanks very much for sticking with me on this.

Final point, however. If I have the following numbers:-

2
1
3
44
25

...the combo sorts them as:-

1
2
25
3
44

...rather than 1 2 3 25 44 (this seems to do it with dates as well).

Thanks for your time. By the way, are you the same Tom urtis who wrote HOLY MACRO!

Many thanks,
PAUL.

Yes, I am the same Tom Urtis who is one of the authors of the Holy Macros disk. In fact I am working on it today, just taking a break and perusing the message board.

Regarding your question about how the numbers are sorting, let's take a different approach...here's something I just wrote that works for me:

Private Sub UserForm_Initialize()
With Application
.ScreenUpdating = False
Dim asn As String, ItemsRange As Range, cell As Range
asn = ActiveSheet.Name
Columns(1).Value = Sheets(asn).Columns(4).Value
Columns(1).SpecialCells(4).EntireRow.Delete
Rows(1).Insert
Range("A1").Value = "zzzzz"
Set ItemsRange = Range("A1").CurrentRegion
With ItemsRange
ComboBox1.Clear
For Each cell In .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(12)
Next cell
End With
ActiveSheet.Delete
Sheets(asn).Activate
Set ItemsRange = Nothing
ComboBox1.ListIndex = 0
.ScreenUpdating = True
End With
End Sub

Thanks Tom, that's exactly what I required.

You've been a great help and good luck with your writing.

Cheers,
PAUL.

Thanks for the well-wishes. True I'm a little biased, but that CD of 1900 (and growing) examples is among the very best VBA compilations anywhere in my opinion. A great investment for Excel users at any level of expertise.

Replies
5
Views
348
Replies
14
Views
760
Replies
3
Views
476
Replies
1
Views
575
Replies
9
Views
113

1,203,605
Messages
6,056,251
Members
444,853
Latest member
sam69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back