VBA Excel 2010 Listobject autofilter: How can I check if a sort is active in a column

Question

Hi @ all

I would like save all my autofilter setting. My problem is:  How I can dedect in which column is a sort decending or accending  activated ?

Have someone a code snippet for me?

Thanks

Ferby

Answer 1

Hello Ferby,

The following example might help. Note that it loops through the SortFields; not through the List Object columns. I have included the sort key and whether it is ascending or descending and you should be able to extract the columns from the key.

Note: On the line "If .SortFields(c).SortOn = xlSortOnValues Then"   If you place your cursor at the = sign and delete the = sign and re-type it then the intellisense will kick in and display additional options for the type of sorting. If you are using multiple types of sorting then you could replace the If/Then with a Select Case.

Sub LstObjSortInfo()
    Dim ws As Worksheet
    Dim lstObj As ListObject
    Dim c As Long
    Dim strKey As String
    Dim strOrder As String
   
    Set ws = Worksheets("Sheet1")
   
    Set lstObj = ws.ListObjects("Table1")
   
    If lstObj.Sort.SortFields.Count > 0 Then    'Test if table is sorted
        For c = 1 To lstObj.Sort.SortFields.Count   'Loop through the sort fields
            With lstObj.Sort
               
                If .SortFields(c).SortOn = xlSortOnValues Then
                    strKey = .SortFields(c).Key.Address
                   
                    If .SortFields(c).Order = xlAscending Then
                        strOrder = "Ascending"
                    Else
                        If .SortFields(c).Order = xlDescending Then
                            strOrder = "Decending"
                        End If
                    End If
                   
                    MsgBox "Sort field " & c & " Key = " & Chr(9) & strKey & vbCrLf & _
                            "Sort field " & c & " Order = " & Chr(9) & strOrder
                End If
            End With
        Next c
    Else
        MsgBox "Table " & lstObj.Name & " is not sorted"
    End If
           
End Sub

---

Regards, OssieMac

Answer 2

Hi OssieMac

yes this is the answer what I Need. Thanks :-)