Capturing Sort order..

[garyj]

I have a table with a Column for the "Trip No". The names respectively are BkgTbl and BkgTripNo.
The field is a string because it consists of a 6 digit number which in some cases is followed by a single digit letter. Eg: 240001, 240002, 240003a, 240003b, 240004

When using the autofilters, the order would be rendered 240001, 240002, 240004, 240003a, 240003b... rather than what I want above.

I thought about adding another filter button, with a macro to sort.
But, then came the thought to hijack the autofilter sort that comes with the table. Most people say it isn't possible to capture a sort. But then I found this on the following site:
https://stackoverflow.com/questions/...89764#77989764

    Dim filterRangeNoHeaders As Range
    Set filterRangeNoHeaders = Range("BkgTripNo")
    If VarType(Target.Value) = 8204 Then
        If Target(1, 1) <> "Trip No" Then Exit Sub
        If Not Application.Intersect(filterRangeNoHeaders, Target) Is Nothing Then
            MsgBox "Add code here"
            End If
       End If

I altered it slightly, but I don't think in a way to change the results.

It does trap the sort, after which I could do my own sort... (which is still a ball of mud in my head at this point).
But in testing this code I found that it traps all the sorts the same way. Target.address becomes the entire header row, and thus target(1,1) is always "Trip No". The application.intersect line should correct it, but in my testing it captures all the sorts of every field in the table.

Anyone want to tackle this one?

Gary

[Paul_Hossler]

Just a concept

Option Explicit


Sub Test()
    Dim i As Long
    Dim r As Range, r1 As Range, c As Range
    
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
    
    For Each c In r1.Columns(1).Cells
        If Len(c.Text) = 6 Then c.Value = c.Text & Chr(1)
    Next
    
    With r.Parent.Sort
        .SortFields.Clear
        .SortFields.Add Key:=r1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange r
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    For Each c In r1.Columns(1).Cells
        If Right(c.Text, 1) = Chr(1) Then c.Value = Left(c.Text, 6)
    Next


    MsgBox "done"


End Sub

[Aflatoon]

If you sort the column yourself specifying to sort text and numbers separately, that should persist.

Worst case your code could simply loop through the sortfields and set the dataoption to xlSortTextAsNumbers then reapply the sort.

[garyj]

Oops...

[Re Paul] I tried your sort, Paul. I was tired, so didn't notice that I should have recorrected the range, as my sheet has a title with buttons above the table header. So it messed up my table header, put it on the bottom, and a few other things. I went to my OneDrive to get the history and revive a previous sheet, so no worries. But as I was watching what you were doing, I was impressed. Since the letter at the end of [Trip No] is there only to separate a 2nd or 3rd (a,b,c) bus on the same trip, I figured I would write a code that would use 'z', add it to strings that were len=6, do the sort, then remove the z's. It's basically what you were doing, but more in my level of understanding. Me not so smart as you - haha. Anyway, I loved the idea. Give them all a letter and it sorts perfect... then remove the letter. Anyway, since I am making a table that could equate to as many as 10,000 rows, I decided to do a test, and as much as I love this idea, it was 20 times slower every time. Thanks much though. I have learned much from you.

[Re Aflatoon] Many thanks as well. I couldn't seem to make the xlSortTextAsNumbers idea work. I am not sure why, but it sorted the same way it does when I use the sort function of the autofilter button above the column. But as to your other suggestion, and while I don't like using clumsy helper columns because they can be discovered by a user, I decided to make one and check out the time. I made 2. Sort1 = LEFT([@Column1],6) and Sort2 =IF(LEN([@Column1])=6,"",RIGHT([@Column1],1)). Then I applied this code, which could possibly be simplified, and it came in as my one of the fastest methods each time. I had a third method, similar, slightly different code, which timed about the same.

Sub sort2()
    ActiveWorkbook.Worksheets("Sheet6").ListObjects("BkgTbl").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet6").ListObjects("BkgTbl").Sort.SortFields.Add2 _
        Key:=Range("BkgTbl[[#All],[Col3]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet6").ListObjects("BkgTbl").Sort.SortFields.Add2 _
        Key:=Range("BkgTbl[[#All],[Col4]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets("Sheet6").ListObjects("BkgTbl").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I don't think this code contains unnecessary bloat, or how much it could be simplified. I struggle with when to use Sheets.("Booking").ListObjects("BkgTbl") and when to do it without the ListObject. I also don't have a good memory for what all is under the table and what is under the sheet. Anyway, this tells you where I am at, in case you have some wisdom to offer.

My first question though is unanswered. Haha. Serves me right for saying anything about working on a sort code :P
The code that I showed in my first post could be used to hijack the pressing of any autofilter button. I want to narrow it to only the first column, which is labeled [Trip No] and is named as a range [BkgTripNo]. When I click to sort any other column in the table, I get the MsgBox result: "Add code here". Do you have any ideas how to change the code so that the other table sort buttons are passed over, and only the first column button continue with the code?

Here is what I know about it so far.
- when I break the flow and F8 through it, no matter which button I press, target.Address = $A$2:$AY$9. That is the header row.
- filterRangeNoHeaders = Range("BkgTripNo") so filterRangeNoHeaders.Address = $A$3:$A$9
- I just now realized they do intersect, and that is why every test moves on successfully through that line.

Ideas?

Gary

[garyj]

I also found this, which is a code to hide a single field autofilter dropdown. So maybe there is a way to test the target with the field no? I am dreaming haha.

Sub AutoFilter()


With Range("A1")
     .AutoFilter Field:=1, VisibleDropDown:=Ture
     .AutoFilter Field:=2, VisibleDropDown:=True
     .AutoFilter Field:=3, VisibleDropDown:=True
     .AutoFilter Field:=4, VisibleDropDown:=False    
     .AutoFilter Field:=5, VisibleDropDown:=True
     End With


End Sub

[Paul_Hossler]

Your choice. Personally -- and it is purely my own opinion -- I find that if I can keep a macro very simple and don't try to impress myself with how clever I am when I come back to it in 6 months I can understand what it's supposed to do

I get confused trying to go through lots of ['s and [['s and #'s.


If you're still interested ...

If you have that many rows, this should be a faster version of my first attempt.

Option Explicit


Sub Test()
    Dim i As Long
    Dim r As Range, r1 As Range
    Dim v As Variant
    
    
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
    
    v = Application.WorksheetFunction.Transpose(r1.Columns(1).Value)
    
    For i = LBound(v) To UBound(v)
        v(i) = Format(v(i))
        If Len(v(i)) = 6 Then v(i) = v(i) & Chr(1)
    Next i


    r1.Columns(1).Value = Application.WorksheetFunction.Transpose(v)


    With r.Parent.Sort
        .SortFields.Clear
        .SortFields.Add Key:=r1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange r
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    v = Application.WorksheetFunction.Transpose(r1.Columns(1).Value)
    
    For i = LBound(v) To UBound(v)
        If Right(v(i), 1) = Chr(1) Then v(i) = Left(v(i), 6)
    Next i


    r1.Columns(1).Value = Application.WorksheetFunction.Transpose(v)
    
    MsgBox "done"


End Sub

[garyj]

Please don't worry about it too much. I chose to make a sort button and place it at the top of the cell, rather than over the other. This way one can be used as a filter and the other as sort, and I can just teach the difference.

My code to make the button a sort toggle is as follows...

Sub SortbyNumberAlpha()
    Dim wks As Worksheet
    Dim tbl As ListObject
    Set wks = Sheets("Booking")
    Set tbl = wks.ListObjects("BkgTbl")
    Dim lastRw As Integer
    lastRw = tbl.ListRows.Count
    tbl.Sort.SortFields.Clear
    'Sheets("Booking").ListObjects("BkgTbl").Sort.SortFields.Clear
    If Cells(3, Range("BkgTNSort1").Column).Value > Cells(lastRw, Range("BkgTNSort1").Column).Value Then
        tbl.Sort.SortFields.Add2 Key:=Range("BkgTNSort1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        tbl.Sort.SortFields.Add2 Key:=Range("BkgTNSort2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Else
        tbl.Sort.SortFields.Add2 Key:=Range("BkgTNSort1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        tbl.Sort.SortFields.Add2 Key:=Range("BkgTNSort2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        End If
    With tbl.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

[Aflatoon]

If the sort text as numbers option doesn't work, then perhaps you actually have your numbers stored as numbers, not text.

[Paul_Hossler]

FWIW, my observation is that it's almost always necessary to sanitize / standarize data when it's just imported or pasted into Excel worksheets

Anything that looks like a number (20002) becomes a number, anything that that looks like a date (4/5) becomes a date. Text at least usually remains text

20004b and 20002+Chr(1) are both strings and sort as strings

[garyj]

If the sort text as numbers option doesn't work, then perhaps you actually have your numbers stored as numbers, not text.

Hmm... the format is General. Does that mean that some are stored as text (i.e. 240001) and some as string (i.e. 240002a)?
I had assumed that if any cell in the column is string, they would all be string. Dang assumptions!

[Aflatoon]

Excel is not a database, so it has no concept of field types. Also, the format of a cell doesn't necessarily reflect the data type of the value in it.

[Paul_Hossler]

Post #9