Private Sub UserForm_Initialize()
Dim usedRng As Range
Dim ws As Worksheet
Dim FirstRow As Long, FirstCol As Long, LastRow As Long, LastCol As Long
Dim vaItems As Variant
Dim i As Long, j As Long
Dim c As Integer
Dim vTemp As Variant
Set ws = Sheets("List")
FirstRow = ws.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
FirstCol = 1
LastRow = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastCol = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Set usedRng = ws.Range(ws.Cells(FirstRow, FirstCol), ws.Cells(LastRow, LastCol))
ListBox1.RowSource = "List!" & usedRng.Address
vaItems = ListBox1.List
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
If vaItems(i, 2) > vaItems(j, 2) Then
For c = 0 To ListBox1.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
Next j
Next i
ListBox1.RowSource = ""
ListBox1.List = vaItems
End Sub
example-data-for-listbox.xls
Title | # Comments | Views | Activity |
---|---|---|---|
How to compare 3 columns in excel? | 7 | 46 | |
Delete records using AutoFilter | 6 | 15 | |
Extracting Text within 2 Symbols in Excel | 5 | 29 | |
Merging multiple rows to one | 22 | 32 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
6 Experts available now in Live!