Sorting macro

[sspatriots]

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

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

 

[OrangeYoda]

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.

 

[Peter_SSs]

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.

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

 

[Alex Blakenburg]

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)

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

 

[Peter_SSs]

Using Alex's idea of converting to text, sorting then converting back to original format, what about this as another option?

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.

 

[sspatriots]

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

 

[Alex Blakenburg]

Thanks for the update. Glad we could help.
Let me know how go with your testing.

 

[sspatriots]

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:

With tblPO.ListColumns("PO'#").DataBodyRange

 

[sspatriots]

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")".

 

[Peter_SSs]

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
	D	E	F	G	H	I	J	K	L
1
2
3		PO#	A	B		PO#	A	B
4		5017	5	5		0147 R1	9	29
5		1484 R2	12	20		0789 R2	10	30
6		5013	1	21		1483 R1	11	31
7		5014	2	22		1484 R2	12	20
8		5019	7	27		1497 R2	13	33
9		5020	8	28		1786 R1	14	34
10		0147 R1	9	29		1807 R1	15	35
11		0789 R2	10	30		1820 R1	16	36
12		5018	6	31		2327 R1	17	37
13		1483 R1	11	31		5013	1	21
14		1497 R2	13	33		5014	2	22
15		1786 R1	14	34		5015	3	36
16		1807 R1	15	35		5016	4	88
17		5015	3	36		5017	5	5
18		1820 R1	16	36		5018	6	31
19		2327 R1	17	37		5019	7	27
20		5016	4	88		5020	8	28
21
2022

Cell Formulas
Range	Formula
I4:K20	I4	=SORTBY(Table46,LEFT(Table46[PO'#],4))
Dynamic array formulas.