VBA Sorting the ListBox by values.

[asyamonique]

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

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

 

[Fluff]

How about

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

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]

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]

Ok, how about

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

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

 

[asyamonique]

Thats great
Many Thanks

 

[Fluff]

You're welcome & thanks for the feedback.