Sort Rows by Mid() string of column cells

[NMeeker]

I have a single column dynamic range called "Dock" and I need to sort the rows in my worksheet based on information from this range.

Each cell in "Dock" is formated similarly to "TM09030010"
This is "TM" & YYMM & "4 digit number"

I need to sort my rows based first on Date "YYMM" then second by the last 4 digits of the same string.

The worksheet has information in columns A through K and Range "Dock" is located within column A.

Any help would be appreciated

 

[NMeeker]

Why not to add an extra column to prepare the filter tehn remove this column at the end.
How do you make the distinction between 1900 and 2000 dates, is only 9 after TM the reason.

K not sure what you meant by the first sentence...
but... If you are asking why I am adding the column, then deleting it. I do not want the user to see it on their worksheet. I also don't want to leave the formulas on the worksheet, Would prefer not to hide columns either if I can avoid it. Or are you saying I should insert an ENTIRE 2 Columns for the sort. How would I do that?
I suppose I should also Add a statement Like

Application.ScreenUpdating = False
'Run code here
Application.ScreenUpdating = True

As for the second sentence....
but To distinguish between 1900s and 2000s
The dates are set up as YYMM so... a 1900 (If data starts in 90s) would be "9801" I used the If statement to look for the first number "9"

The flaw to this method would be If they use this program past the year 2089 LOL but I don't think that will be a problem. Though it would only affect a small part of the overall program.

 

[PCL]

Reusing your formula to recreate the date give a chance to next code:

Option Explicit
Sub Filter()
Dim LAST_Col As Integer
Dim LAST_Row As Long
    LAST_Col = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(1, LAST_Col + 1) = "MyFilter"
    LAST_Row = Range("A" & Rows.Count).End(xlUp).Row
    Range(Cells(2, LAST_Col + 1), Cells(LAST_Row, LAST_Col + 1)).Formula = "=IF(MID(A2,3,1)=""9"",19,20) & MID(A2,5,2) & RIGHT(A2,4)"
    Rows("1:1").AutoFilter
    Range(Cells(1, 1), Cells(LAST_Row, LAST_Col + 1)).Sort Key1:=Cells(1, LAST_Col + 1), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Rows("1:1").AutoFilter
    Columns(LAST_Col + 1).Delete
End Sub

 

[NMeeker]

Wow, Thanks PLC... I have never seen the .AutoFilter before. I had no idea what it was or how to use it. Unfortunately, It didn't do the trick. And I had some fun cleaning up the mess. hehehe...

Trouble is, I only want information the rows within the dynamic Range "Dock" to be sorted.

Worksheet is layed out similarly to:

Data I don't want to touch.....
Data I don't want to touch.....
Data I don't want to touch.....

Range("Dock") = TM03010040 Other Data Other Data
Range("Dock") = TM08030020 Other Data Other Data
Range("Dock") = TM98010040 Other Data Other Data

Data I don't want to touch.....
Data I don't want to touch.....
Data I don't want to touch.....
​

I want to sort ONLY the rows used by Range("Dock")
And I am trying to sort by the value of the range... which would be the TM#

TM#'s are designed by "call" "YYMM" "ref#"
where "call" is a 2 letter call name like TM or CR
"YYMM" Is the year and month
"ref#" Is a 4 digit number
TMYYMM####

Sub DocketSort(SheetName As String)
Dim Cel As Range
Application.ScreenUpdating = False
Range("B:C").Insert
For Each Cel In ThisWorkbook.Worksheets(SheetName).Range("Docket")
    If Left(Cel.Value, 2) Like "TM" Then
        Cel.Offset(0, 11).Value = "=DATE(IF(MID(A" & Cel.row & ",3,1) = ""9"", MID(A" & Cel.row _
        & ",3,2),20 & MID(A" & Cel.row & ",3,2)),(MID(A" & Cel.row & ",5,2)),1)"
        Cel.Offset(0, 12).Value = "=MID(A" & Cel.row & ",7,4)"
    End If
Next



'Need Selection.sort on Column B as a date then Column C as Number
'BUT!!! This Needs to ONLY sort the information within the rows referenced by Range("Dock")

Range("B:C").Delete

Application.ScreenUpdating = True
End Sub

This code gives me 2 columns. The first, B, has dates only on rows referenced by "Dock" And the second column, C, has the ref# on the same rows

Is there a way to sort ONLY the rows that are part of my range "Dock"?
Dock is a DYNAMIC range... so it will change in size.
So hard coding specific rows will not work.

Also, could I sort based on Dates?
And can I sort based on 2 columns?

Any other suggestions? Please help... Any Ideas seem to get me a little closer...

 

[PCL]

Is the Range("Docket") continuous. For example all columns from F to J and all rows from 101 to 1001.
Could you give a short example.
Perhaps SORT could be used.
Why to prepare 2 columns (Date and reference) and not only one = Date & ref.

 

[PCL]

Here 2 macros which could help.
The first one is preparing an extra column for the filter as you are doing: Only one to keep example simpe.
The second is sorting the range Docket using the extra column.

Sub Extra_Column_Prepa()
Dim LAST_Col As Integer
Dim LAST_Row As Long
    LAST_Col = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(1, LAST_Col + 1) = "MyFilter"
    LAST_Row = Range("A" & Rows.Count).End(xlUp).Row
    Range(Cells(2, LAST_Col + 1), Cells(LAST_Row, LAST_Col + 1)).Formula = "=(IF(MID(A2,3,1)=""9"",19,20) & MID(A2,5,2) & RIGHT(A2,4))*1"
End Sub
Sub Sort()
Dim Docket As Range
Dim My_Row
    Set Docket = Range("A6:E15")
    My_Row = Docket.Row
    With Docket
        .Sort Key1:=Range("E" & My_Row), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    End With
End Sub