How to sort alphabetically a Range Variable?

[pablo_the_sonar]

Hello everybody!

I am relatively a newbie to VBA and completely new on this forum, so I preventively apologise for any odd manipulation or obvious question I might ask here...

So here we go: I am working on a table to manage Human Resources at work, where each colleague has a specific row. In the table, I have two contiguous columns, the first one indicating the exact position title (i.e. Administrator, Head of office, etc), and the second one the department in which the person works (i.e. Management, Administration, Logistics, etc). To be noted that these columns are NOT sorted out alphabetically in the worsheet.

I have created an userform with a Listbox, which I want to populate with the data coming from the two columns mentioned above. I would like this Listbox to be populated avoiding double entries with the same position title (e.g. if two persons have the "administrator" title, this should appear only once in the listbox). To do so, I used a "Range" variable in the userform_initialize subroutine, which works pretty well.

I would like the Range to be sorted out alphabetically BEFORE is inserted in the listbox - which I haven't managed so far. Browsing the web I've found a few threads dealing with this topic, but I'm not sure I've found something really answering my question. Is there a simple way (5-6 lines of code max) to do this?

Here's my code in its actual state:
___________________________________________________________________________________
Private Sub UserForm_initialize() 'create a list of positions existing in the table (without doubles)


'___ONE: set the variables
Dim iRangeDpt As Range
Dim iColumn As Range

Dim n As Integer

'___TWO: set the entire "position" column as reference
Set iColumn = Range(Range("cell_mt_column_05_position").Offset(1, 0), _
Range("cell_mt_column_05_position").Offset(1, 0).End(xlDown))

'___THREE: then add positions individually without doubles to iRangeDpt Variable
n = 1
For Each i In iColumn
If n = 1 Then
Set iRangeDpt = Cells(i.Row, i.Column + 1)
ElseIf Application.CountIf(Range(i, i.Offset(-(n - 1), 0)), i) = 1 Then
Set iRangeDpt = Union(iRangeDpt, Cells(i.Row, i.Column + 1))
End If
n = n + 1
Next i

'___FOUR: order the iRangeDpt Range in alpabetic order
THIS IS WHERE I WOULD LIKE TO INSERT THE SORTING CODE

'___FIVE: define the listbox specifics
list_position_names.ColumnCount = 3
list_position_names.ColumnWidths = "20;80;140"


'___SIX: add list to the userform
n = 0
With list_position_names
For Each i In iRangeDpt
.AddItem
If n < 9 Then
.List(n, 0) = "0" & n + 1
Else
.List(n, 0) = n + 1
End If
.List(n, 1) = Right(i, Len(i) - 5)
.List(n, 2) = i.Offset(0, -1)
n = n + 1
Next i
End With

End Sub
___________________________________________________________________________________

Many thanks in advance for any help and support in this!

Warm regards

Pablo

 

[pablo_the_sonar]

UPDATE: I circumvented the problem by sorting the range alphabetically AFTER populating the listbox with it. However, I am still curious to know wheter a Range variable can be sorted out alphabetically in a simple way. Thanks!

Ciao

Pablo