Sorting macro

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
547
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've recorded a sorting macro (below). However, when I try and run it the cells that end with an "R#" move to the end of the sort. Is there a way to make this sort the table column on just the first 4 characters of the PO#? I know I can add a helper column and sort on that, but I'm afraid it may mess up another spreadsheet that references this table. Thanks, SS

Sample of my values and how they are sorting in ascending order:

5013
5014
5015
5016
5017
5018
5019
5020
0147 R1
0789 R2
1483 R1
1484 R2
1497 R2
1786 R1
1807 R1
1820 R1
2327 R1



VBA Code:
Sub SortPONo()
'
' Sort PO Numbers Macro
'

'
    ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Add2 _
        Key:=Range("Table46[PO'#]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
VBA isn't my strength by a long shot. However Excel does have a left, right, middle function. I'm assuming VBA has a similar argument you can use. If it was in a cell it would be as simple as adding a =left(cell, 4) to your statement. Maybe that will get you on the right path at least.
 
Upvote 0
An easy way would be to add another column to your table, say a column headed 'POSort', populate it with the formula =LEFT([@[PO'#]],4) , hide that column if you want, and change your existing code to sort on that new column instead.

Rich (BB code):
Sub SortPONo_v2()
'
' Sort PO Numbers Macro
'

'
    ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort.SortFields.Add2 _
        Key:=Range("Table46[POSort]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("2022").ListObjects("Table46").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0
It is going to depend on how your data is refreshed both in this table and any tables using Lookups to/from this table eg do they arrive as numerics or as text.
Adding a column to the end as Peter suggested is unlikely to impact any lookups since presumably they are already looking up the numbers as numbers and are unlikely to reference any additional columns.

If you really don't want to use a helper column then you can try this:
(it converts the numbers to text pre-sort and then back again post-sort, if the data type in associated tables is text you may want to leave it as text and not convert it back)

VBA Code:
Sub SortPONo()
'
' Sort PO Numbers Macro
'
    Dim tblPO As ListObject
    Set tblPO = ActiveWorkbook.Worksheets("2022").ListObjects("Table46")
    
    With tblPO.ListColumns("PO'#").DataBodyRange
        ' Convert numbers to text
        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, xlTextFormat), TrailingMinusNumbers:=True
        ' Set format to text for future additions
        ' .NumberFormat = "@"                           '<--- * Uncomment this if you decide you want to keep it as text in the future
    End With
    
    With tblPO
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 _
            Key:=.ListColumns("PO'#").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
            
    ' Assuming you want the numbers turned back into numbers because associated data has them as numbers
    ' * if you want to keep them as text comment this out or remove it
    With tblPO.ListColumns("PO'#").DataBodyRange
        ' Reset Column Format to General
        .NumberFormat = "General"
        ' Convert text back to numbers
        .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, xlGeneralFormat), TrailingMinusNumbers:=True
    End With
End Sub
 
Upvote 0
Solution
Using Alex's idea of converting to text, sorting then converting back to original format, what about this as another option?

VBA Code:
Sub SortPONo_v3()
  With ActiveWorkbook.Worksheets("2022").ListObjects("Table46").ListColumns("PO#")
    .Range.Value = Evaluate("""|||""&" & .Range.Address)
    .Parent.Range.Sort Key1:=.DataBodyRange, Order1:=xlAscending, Header:=xlYes
    .Range.Replace What:="|||", Replacement:="", LookAt:=xlPart
  End With
End Sub

However, returning to my idea of the extra column extracting the first 4 characters for a moment. If you did that and left that column visible, than you may not need a macro at all since that column could be used at any time to sort the table by using the sort option in the header drop-down of that extra column.
 
Upvote 0
Thanks for the great ideas everyone. I went ahead and added the extra column to the end of my table. However, when I have a little time this week, I'm planning to give what Alex has suggested a try. I will post back. Peter's idea did work, so thanks again for that. SS
 
Upvote 0
I plugged the code in that works without the helper column. I get a "Run-time error '9': Subscript out of range" at the line below:

VBA Code:
With tblPO.ListColumns("PO'#").DataBodyRange
 
Upvote 0
I tried taking out the apostrophe in "PO'#" and it did sort properly for the most part. The only issue now is a lot of those cells are formulas with hyperlink address, so it sorted those to the top. They all start with "=HYPERLINK("\\....". Is there away around that? The value is at the end of the formula. For example the end of the formula looks like "...Building.pdf","2327 R1")".
 
Upvote 0
a lot of those cells are formulas
If they are in the PO# column then I suspect that the suggestions in posts 4 or 5 will be suitable.

Another possibility might be that something like this would suit you (not tested with hyperlinks)?
Leave the original table as-is and reproduce it in the wanted order with a formula.

sspatriots_1.xlsm
DEFGHIJKL
1
2
3PO#ABPO#AB
45017550147 R1929
51484 R212200789 R21030
650131211483 R11131
750142221484 R21220
850197271497 R21333
950208281786 R11434
100147 R19291807 R11535
110789 R210301820 R11636
1250186312327 R11737
131483 R111315013121
141497 R213335014222
151786 R114345015336
161807 R115355016488
175015336501755
181820 R116365018631
192327 R117375019727
2050164885020828
21
2022
Cell Formulas
RangeFormula
I4:K20I4=SORTBY(Table46,LEFT(Table46[PO'#],4))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top