Apologies Paul for not specifying the number of rows; I will be more mindful of this in future posts.
No problem - my personal first approach is to go with a simple approach, even if it's not as efficient as others. For 1000+ rows, I doubt there would be a perceptible wall clock time difference
280k rows requires a more efficient approach as P45cal and snb have said
I used P45cal's macro and 'generalized' it a bit to sort the block of data (.CurrentRegion) by the first column, and I assumed that you had headers
For testing in the attached (since the run time issue has been fixed) I just used a dozen rows and 14 columns, so try it with your real data
Option Explicit
Sub drv()
Call LetterNumberSort(ActiveSheet.Cells(1, 1).CurrentRegion)
End Sub
' composed of 1 to 3 letters, and then a number, with the length of this number ranging from 1 to 5 digits.
Sub LetterNumberSort(r As Range)
Dim i As Long
Dim aryValues As Variant
Dim s As String
Dim r1 As Range
With r
Set r1 = .Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count)
aryValues = .Columns(1).Value
For i = 2 To UBound(aryValues, 1)
Call pvtFormatToSort(aryValues(i, 1))
Next
.Columns(1).Value = aryValues
With .Parent.Sort
.SortFields.Clear
.SortFields.Add Key:=r1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange r
.Header = xlYes ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
aryValues = .Columns(1).Value
For i = 2 To UBound(aryValues)
Call pvtFormatToDisplay(aryValues(i, 1))
Next
.Columns(1).Value = aryValues
End With
End Sub
'1. objects are always passed ByRef
'2. r should be Dim-ed as a Range since it is a Range, and not a Variant
'3. I originally had these as Functions, but that way forces VBA to make copies of the strings
Private Sub pvtFormatToSort(r As Range)
Dim s1 As String, s2 As String, s3 As String
s1 = Trim(UCase(r))
If s1 Like "[A-Z]#*" Then
s2 = Left(s1, 1) & "00"
s3 = Right("00000" & Right(s1, Len(s1) - 1), 5)
ElseIf s1 Like "[A-Z][A-Z]#*" Then
s2 = Left(s1, 2) & "0"
s3 = Right("00000" & Right(s1, Len(s1) - 2), 5)
ElseIf s1 Like "[A-Z][A-Z][A-Z]#*" Then
s2 = Left(s1, 3)
s3 = Right("00000" & Right(s1, Len(s1) - 3), 5)
End If
r = s2 & s3
End Sub
Private Sub pvtFormatToDisplay(r As Range)
Dim s1 As String, s2 As String, s3 As String
Dim i As Long
s1 = r
If s1 Like "[A-Z]#*" Then
i = 2
ElseIf s1 Like "[A-Z][A-Z]#*" Then
i = 3
ElseIf s1 Like "[A-Z][A-Z][A-Z]#*" Then
i = 4
End If
s2 = Left(s1, i - 1)
Do While Mid(s1, i, 1) = 0
i = i + 1
Loop
s3 = Right(s1, Len(s1) - i + 1)
r = s2 & s3
End Sub