Answered by:
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
- 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 SubRegards, 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 SubRegards, 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