Excel VBA Primary Sort by Last Character, Secondary Sort by Number (Alpha-Numeric)

[KnightFhawker]

Hi All!

I am trying to find a way in Excel VBA to Sort my unique list primarily by the last character, which in this case is a letter, then secondarily by the preceding number. I haven't been able to find this as an option so far so hopefully someone will be able to help me with this. If it is not directly an option, I was thinking that maybe there is a way to add a part to my unique filter initially where it first filters a unique list with only the last character, then another unique filter to list the other last character. (There is only an A and B in the last character position so this would only need two sweeps through to complete the list).

Here's my unique listing code I am currently using:

Sheets("Master").Range("AA6", Range("AA" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Result in Column AA:

101A
102A
103A
104A
105A
1B
201A
2B
3B


Basically I just want to list all of the A's together first and then list the B's together after.

Still a bit of a newbie here so I hope I formatted everything okay. If there's anything else you need from me to be able to assist just let me know.

Thanks in advance for your help!

 

[Joe4]

Why not use a helper column that pulls the last character out, and use that in your sorting, i.e.

=RIGHT(AA6,1)

 

[KnightFhawker]

Hi Joe4,

Thanks for your response. If it comes down to it then I will probably end up having to use a helper column, but I really try to minimize using them generally since they usually just add more unnecessary steps and columns. I tend to only use them if there is no other way, or for more complex multi-worksheet referencing. I would like to try to keep this sheet as clean as possible, but also I just love learning new ways to approach an issue, and learn new coding options and features to expand my knowledge. Also, since this list range will be dynamic and needs to be automatic upon execution, it seems like the steps and coding involved with a helper column will still be somewhat complicated and possibly resource heavy in the end. However, if sorting or adjusting the unique filter in this way is not possible or extremely complicated I will just move forward with the helper column option.

Thanks again Joe4.

 

[Joe4]

Also, since this list range will be dynamic and needs to be automatic upon execution, it seems like the steps and coding involved with a helper column will still be somewhat complicated and possibly resource heavy in the end.

Not really. Adding one helper column dynamically via VBA is a pretty easy and straightforward task, and will probably be less resource heavy than some complicated formulas or using Loops in VBA.

However, if you want to look at other options, check out this link:
https://www.extendoffice.com/documents/excel/925-excel-sort-last-character.html