Sorting macro

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
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
 
Its a common issue for us that in simplying the example posted here, the data becomes unrepresentative of the real data.
We definitely need to know if the data is the result of a formula or is a constant and whether the data is text or numeric or a mix.

I am assuming you are not hard coding the PO number in the Hyperlink formula as indicated in your example.

Just on the off chance that you have in fact already converted everything to text, in your original code in post #1 try replacing your xlSortTextAsNumbers with xlSortNormal
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Its a common issue for us that in simplying the example posted here, the data becomes unrepresentative of the real data.
We definitely need to know if the data is the result of a formula or is a constant and whether the data is text or numeric or a mix.

I am assuming you are not hard coding the PO number in the Hyperlink formula as indicated in your example.

Just on the off chance that you have in fact already converted everything to text, in your original code in post #1 try replacing your xlSortTextAsNumbers with xlSortNormal
Some of the data (hyperlinks) are the result of a formula linked to a button that allows the user to select the cell they want to hyperlink and it opens to a folder where they can select the file they are linking. Only issue is that it puts the link on the spreadsheet in the form of a formula like I had in one I put in post #9. I'm posting the code that creates that hyperlink below. Maybe someone can tell me how to create this link in this code such that it doesn 't leave a formula in the cell and just the text with the hyperlink.

Here is the code:

VBA Code:
Sub HyperlinkFile()
Dim xGetFile As Object
Dim fName As String, sFullFilename As String, sFileName As String

'Opens dialog box to Pick File to Hyperlink

Set xGetFile = Application.FileDialog(msoFileDialogFilePicker)


        xGetFile.InitialFileName = "\\company.local\drives\Manufacturing\Commercial Archive\" 'Selected File full path


With xGetFile
    .Title = "Spreadsheet Creative - Select File to Hyperlink"
    If .Show = -1 Then
'Selected File full path
        fName = .SelectedItems(1)
        xGetFile.InitialFileName = "\\company.local\drives\Manufacturing\Commercial Archive\" 'Selected File full path

       
    Else
        fName = ""
    End If
End With

'Test if user pressed cancel
If fName = "" Then Exit Sub
'Resume code

'Get File name from path
sFullFilename = Right(fName, Len(fName) - InStrRev(fName, "\"))
On Error Resume Next

'Change display text to the Selected File name WITHOUT FILE EXTENSION
'if you want to display the file extension then change the formula from sFileName to sFullFilename
'sFileName = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))

sFileName = ActiveCell.Text

On Error Resume Next

'Add HYPERLINK formula to active cell
ActiveCell.Formula = "=HYPERLINK(""" & fName & """,""" & sFileName & """)"

End Sub
 
Upvote 0
Firstly, did you try your original code changing the sort to xlSortNormal ?

Secondly on the Hyperlink, it probably should be a separate question but just recording the macro will pretty much get you there.
Just replace your last ActiveCell.Formula line with this one:
VBA Code:
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, _
                          Address:=fName, TextToDisplay:=sFileName
 
Upvote 0
Firstly, did you try your original code changing the sort to xlSortNormal ?

Secondly on the Hyperlink, it probably should be a separate question but just recording the macro will pretty much get you there.
Just replace your last ActiveCell.Formula line with this one:
VBA Code:
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, _
                          Address:=fName, TextToDisplay:=sFileName
I did try the xlSortNormal. That is where I actually started having the sort issues. It would put everything with the "R#" suffix at the bottom of my sort list and I need everything to sort on the first for numbers of the cells in that column and ignore the "R#"s.

Before I saw your last post above, I actually did try and record the steps, however, it gave me the actual file path when it was recorded. So I substituted it with "fName" and it worked. I removed everything else. Below is what worked for me. I'll will post back if everything is working after I make these few changes. Thank you again for all your help.

VBA Code:
'Add HYPERLINK formula to active cell
'ActiveCell.Formula = "=HYPERLINK(""" & fName & """,""" & sFileName & """)"

'Add Anchored HYPERLINK to active cell
    With ws
    
     .Hyperlinks.Add Anchor:=Selection, _
     Address:=fName
     
    End With
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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