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

  • 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


    • Edited by Ferby_ Thursday, April 9, 2015 1:17 PM
    Thursday, April 9, 2015 1:16 PM

Answers

  • 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

    • Marked as answer by Ferby_ Wednesday, March 9, 2016 9:09 AM
    Sunday, April 12, 2015 2:08 AM

All replies

  • 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

    • Marked as answer by Ferby_ Wednesday, March 9, 2016 9:09 AM
    Sunday, April 12, 2015 2:08 AM
  • Hi OssieMac

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

    
    Monday, April 13, 2015 6:18 AM