Excel VBA How To Sort Multi Column List Box With Condition

[kashif.special2005]

Hi,

I have a form and in that form I have a list box with 4 columns Name, Age, Type and TransDate and I want to sort the list box by TransDate with condition.

Sort Condition:- Those TransDate is within before 24 hours by today's date will show first then show rest of the dates below.

Note 1:- In TransDate column there is also NULL.

Note 2:- In TransDate column Dates may also be futures date

ListBox Name is "QCList"

Please help me to resolve this problem.

Thanks
Kashif

 

[kashif.special2005]

Hi,

Kindly request to all expert, please help me or guide me how can I achieve desired result.

Thanks
Kashif

 

[kashif.special2005]

Hi,

I just build the logic to sort the list box with condition, and I just wanted to share the code.

Sub test()


    Dim vData As Variant
    
    vData = Me.ListBox1.List
    
    vData = SortByDate(vData)


    Me.ListBox1.List = vData


End Sub




Function SortByDate(vardata As Variant) As Variant




    Dim i As Long, iLastCount As Integer, iStartCount As Integer
    Dim iLastCol As Integer, iLoop As Integer
    Dim vMydata() As Variant, vMyDataNew() As Variant
    Dim dStart As Date, dEnd As Date
    
    dEnd = Date
    dStart = DateAdd("d", -1, dEnd)
    
    ReDim vMydata(LBound(vardata, 1) To UBound(vardata, 1), LBound(vardata, 2) To UBound(vardata, 2) + 1)
    
    For i = LBound(vardata, 1) To UBound(vardata, 1)
        For iLoop = LBound(vardata, 2) To UBound(vardata, 2)
            vMydata(i, iLoop) = vardata(i, iLoop)
        Next iLoop
    Next i
    
    iLastCol = UBound(vardata, 2) + 1
    
    For i = LBound(vardata, 1) To UBound(vardata, 1)
    
        If vardata(i, 3) >= dStart And vardata(i, 3) <= dEnd Then
            vMydata(i, iLastCol) = 1
        Else
            vMydata(i, iLastCol) = 2
        
        End If
    Next i


    ReDim vMyDataNew(LBound(vardata, 1) To UBound(vardata, 1), LBound(vardata, 1) To UBound(vardata, 2))


    iLastCount = UBound(vardata, 1)
    iStartCount = LBound(vardata, 1)


    For i = LBound(vMydata, 1) To UBound(vMydata, 1)
        If vMydata(i, 4) = 1 Then
            
            For iLoop = LBound(vMydata, 2) To UBound(vMydata, 2) - 1
                vMyDataNew(iStartCount, iLoop) = vMydata(i, iLoop)
            Next iLoop
            iStartCount = iStartCount + 1
        
        Else
            For iLoop = LBound(vMydata, 2) To UBound(vMydata, 2) - 1
                vMyDataNew(iLastCount, iLoop) = vMydata(i, iLoop)
            Next iLoop
            iLastCount = iLastCount - 1
        
        End If
    
    Next i


    SortByDate = vMyDataNew


End Function

Thanks
Kashif