0

Below is the code im using to populate a combo box with unique items only:

Private Sub UserForm_Initialize()

DivisionBox.Clear

Dim LastRow As Long
Dim Rng As Range
Dim Cell As Range

LastRow = Sheets("Active Branch List").Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Sheets("Active Branch List").Range("B3:B" & LastRow)

With CreateObject("Scripting.Dictionary")

For Each Cell In Rng

    If Not .exists(Cell.Value & "-" & Cell.Offset(0, 1).Value) Then
        .Add Cell.Value & "-" & Cell.Offset(0, 1).Value, Nothing
    End If

Next Cell

DivisionBox.List = .keys

End With

End Sub

this works perfectly, except i would like the keys to be sorted A to Z before being added to the combo box. I've searched for solutions but can't seem to work them into the code i already have.

Graham Chandler
  • 175
  • 5
  • 15
  • 2
    open google and type in VBA dictionary Sort next time!! kills me how many people refuse to do a simple search before posting here. http://stackoverflow.com/questions/14808104/sorting-a-dictionary-by-key-in-vba – Sorceri Apr 24 '17 at 19:36
  • 2
    You should also qualify the `Rows.Count` with the sheet you expect it to run on, i.e. `Sheets("Active Branch List").Rows.Count`. – BruceWayne Apr 24 '17 at 19:49
  • 1
    You could use the Range.Sort method, though if you use it with the Rng in your sub, it would sort the cells in worksheet 'Active Branch List". If you don't want to do that, you can create a temporary worksheet and copy the list there, sort it, then load it into your dictionary. But as Sorceri points out, Google/Bing will show this and other ways to do it. – Rich Holton Apr 24 '17 at 22:37
  • Slightly OT, but am I the only one who is mystified that the Scripting.Dictionary doesn't have a sort method? – Rich Holton Apr 24 '17 at 22:38
  • @RichHolton it is definitely odd. hard to believe its this much trouble. i like your idea of sorting the range beforehand. it isn't ideal but is much simpler. – Graham Chandler Apr 25 '17 at 20:59

0 Answers0