Table Manipulation – Sort Table

Sorts an Excel.ListObject (Excel table) by one or more columns specified as a comma-separated list.

The function clears any existing sort fields, adds new sort fields based on the specified columns, and applies the sort with default settings: ascending order, no case matching, top-to-bottom orientation, and PinYin method.

    Public Function xlSortTable(ListObject As Excel.ListObject, 
                                ColumnCSV As String) As String

        Try
            Dim lColumns As List(Of String) = Split(ColumnCSV, ",").ToList

            With ListObject
                .Sort.SortFields.Clear()
                For Each szColumn In lColumns
                    .Sort.SortFields.Add(Key:= .ListColumns(szColumn).Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
                Next
            End With

            With ListObject.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply()
            End With
        Catch ex As Exception
            Return ex.Message
        End Try

        Return String.Empty
    End Function

    Public Function xlSortTable(ListObject As Excel.ListObject, 
                                ColumnCSV As String) As String

        Try
            Dim lColumns As List(Of String) = Split(ColumnCSV, ",").ToList

            With ListObject
                .Sort.SortFields.Clear()
                For Each szColumn In lColumns
                    .Sort.SortFields.Add(Key:= .ListColumns(szColumn).Range, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
                Next
            End With

            With ListObject.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply()
            End With
        Catch ex As Exception
            Return ex.Message
        End Try

        Return String.Empty
    End Function

Posted by ipercube