VBA Sorting the ListBox by values.

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
Hello,
Is it possible to add to below code that I can also sort the datas from Largest to Smallest on the ListBox2 ?
Many Thanks


VBA Code:
Private Sub CommandButton12_Click()
Application.ScreenUpdating = False

On Error Resume Next

Dim J As Long, Dic As Object, rng As Range, Ac As Integer
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
Set rng = Sheets("overtimelist").Range("A:A").CurrentRegion.Resize(, 6)
ReDim nRay(1 To rng.Count, 1 To 6)
For J = 1 To rng.Count
    If Not Dic.Exists(rng(J, 1).Value) Then
       Dic.Add rng(J, 1).Value, rng(J, 5).Text
    Else
        Dic(rng(J, 1).Value) = Dic(rng(J, 1).Value) + rng(J, 5).Value
    End If
        For Ac = 1 To 6
            nRay(J, Ac) = rng(J, Ac).Text
        Next Ac
Next J
With ListBox1
    .ColumnCount = 6
    .ColumnWidths = "140;100;90;100;70;0"
    .list = nRay
End With
With ListBox2
    .ColumnCount = 2
    .ColumnWidths = "150;80"
    .list = Application.Transpose(Array(Dic.keys, Dic.Items))
 End With

Application.ScreenUpdating = True
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Function DicSort(InDic As Object, Optional UpDown As String = "A") As Object
   Dim Ky As Variant
   Dim AryLst As Object
   Dim i As Long
   
   Set AryLst = CreateObject("System.Collections.ArrayList")
   If InDic.Count > 1 Then
      With AryLst
         For Each Ky In InDic.keys
            .Add Ky
         Next Ky
         .Sort
         If UpDown = "Z" Then .Reverse
         Set DicSort = CreateObject("Scripting.Dictionary")
         For i = 0 To .Count - 1
            DicSort.Add .item(i), InDic(.item(i))
         Next
      End With
   Else
      DicSort = InDic
   End If
End Function
and call it like
Rich (BB code):
With ListBox1
    .ColumnCount = 6
    .ColumnWidths = "140;100;90;100;70;0"
    .List = nRay
End With
Set Dic = DicSort(Dic, "Z")
With Listbox2
    .ColumnCount = 2
    .ColumnWidths = "150;80"
    .List = Application.Transpose(Array(Dic.keys, Dic.Items))
 End With
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,126
Hello,
Thanks for the reply, the code works good but I made mistake with my question , I just wanted to ask to sort second column values on ListBox2 from largest to smallest not the first column.
Thanks again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False

'On Error Resume Next

Dim j As Long, Dic As Object, Rng As Range, Ac As Integer, Ary As Variant
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
Set Rng = Sheets("lists").Range("A:A").CurrentRegion.Resize(, 6)
ReDim nRay(1 To Rng.Rows.Count, 1 To 6)
For j = 1 To Rng.Rows.Count
    If Not Dic.Exists(Rng(j, 1).Value) Then
       Dic.Add Rng(j, 1).Value, Rng(j, 5).Value
    Else
        Dic(Rng(j, 1).Value) = Dic(Rng(j, 1).Value) + Rng(j, 5).Value
    End If
        For Ac = 1 To 6
            nRay(j, Ac) = Rng(j, Ac).Text
        Next Ac
Next j
With ListBox1
    .ColumnCount = 6
    .ColumnWidths = "140;100;90;100;70;0"
    .List = nRay
End With
Ary = BsortDescending(Dic)
With Listbox2
    .ColumnCount = 2
    .ColumnWidths = "150;80"
    .List = Application.Transpose(Array(Ary(0), Ary(1)))
 End With

Application.ScreenUpdating = True
End Sub
and
VBA Code:
Function BsortDescending(InDic As Object) As Variant
   Dim Ary As Variant, tmp1 As Variant, tmp2 As Variant
   Dim i As Long, j As Long
   
   Ary = Array(InDic.keys, InDic.items)
   For i = 0 To UBound(Ary(0)) - 1
      For j = i + 1 To UBound(Ary(0))
         If Ary(1)(j) > Ary(1)(i) Then
            tmp1 = Ary(0)(i): tmp2 = Ary(1)(i)
            Ary(0)(i) = Ary(0)(j): Ary(1)(i) = Ary(1)(j)
            Ary(0)(j) = tmp1: Ary(1)(j) = tmp2
         End If
      Next j
   Next i
   BsortDescending = Ary
End Function
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

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