How to sort alphabetically a Range Variable?

pablo_the_sonar

New Member
Joined
Jun 30, 2015
Messages
2
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
 
Last edited:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pablo_the_sonar

New Member
Joined
Jun 30, 2015
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top