Modify Macro to Sort Last Four Characters

[Phil H]

It would be much appreciated if someone could help modify this code to sort by the last four characters. Cells in the range contain entries of this format: N12345-12-A-1234. I need the code to sort by the last four numbers.



Again - appreciate any help.

Private Sub CommandButton23_Click()
    Range("B3:AA39").Select
    ActiveWorkbook.Worksheets("Current Status of CORs").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Current Status of CORs").Sort.SortFields.Add Key:= _
        Range("G4:G39"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Current Status of CORs").Sort
        .SetRange Range("B3:AA39")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

[Pecoflyer]

Could you eventually add a "helper column" with =RIGHT(A1,4) and use that new column to sort ?
(I'm no VBA man myself)

[Phil H]

Can't do that - the data column is part of fixed form

[NoSparks]

Attach a sample workbook so we know what you're dealing with and can test our ideas before posting them.

[Phil H]

Attached data format



Contract Sort.xlsm

[Shashi Kant]

Dear Friend,


Kindly find attachment, it might be useful for you.

[Phil H]

Shashi, Thanks for taking the time to address my problem, much appreciated. Your solution for CommandButton1 works. Now I have to integrate it into my worksheet/format. Originally, I used the macro recorder to create the code, which does a typical ascending sort. In my post, the range of all columns and rows sorted is B3:AA39. The sort column range is G4:G39 (where the contract numbers are held).

Running the code, I get a Run-time error '1004': This formula is missing a range reference of a defined name. Clicking Debug, the "sort now" line is highlighted: Range("A4").Sort....

Column A is not used for data entry.
How to modify the code?

[Shashi Kant]

Dear Friend,

Kindly follow attachment...


it will work according to your range, if you increase your range still it will work or if you decrease your range still it will work.

if not working kindly send your file and your required output on other sheet for my better understanding.

[Phil H]

All I need is to sort the column by the last four numbers.

[Phil H]

Resubmitting uploaded file showing the original worksheet

[Shashi Kant]

I hope it would be worthy now

[Phil H]

Shashi, This works as needed. Thank you for all your time writing this macro - it will improve process efficiency when scanning a list by eye trying to locate a particular number. Again thank you. R/Phil