It may be to you but when looking for free help as on this forum it isn't a good idea to sound demanding.
1. From your post, it isn't possible to tell for sure where column A ends.
It may be to you but when looking for free help as on this forum it isn't a good idea to sound demanding.
Refer also to point 12 of the Posting Guidelines
1. From your post, it isn't possible to tell for sure where column A ends.
2. You may also assist helpers by explaining, and/or giving examples, what you mean by sorting "alphanumerically".
| 1 1640 1646 168 169 486 491 493 494 907g 907h 908ee 908ff 908g 908h |
| 1 |
| 168 |
| 169 |
| 486 |
| 491 |
| 493 |
| 494 |
| 907g |
| 907h |
| 908ee |
| 908ff |
| 908g |
| 908h |
| 1640 |
| 1646 |
Sub SortNumberAlpha()
Dim a
Dim nc As Long, i As Long, ubA As Long
Dim v As Double
Dim s As String
nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
ubA = UBound(a, 1)
For i = 1 To ubA
s = a(i, 1)
v = Val(s)
a(i, 1) = Format(v, String(10, "0")) & Mid(s & "z", 1 + Len(CStr(v)))
Next i
Application.ScreenUpdating = False
With Range("A2").Resize(ubA, nc)
.Columns(nc).Value = a
.Sort Key1:=.Cells(1, nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Columns(nc).ClearContents
End With
Application.ScreenUpdating = True
End SubThanks, that's clearer.
I've assumed there are headings in row 1 and the data starts in row 2. Advise if that is not the case.
Test in a copy of your workbook.
Rich (BB code):Sub SortNumberAlpha() Dim a Dim nc As Long, i As Long, ubA As Long Dim v As Double Dim s As String nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1 a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value ubA = UBound(a, 1) For i = 1 To ubA s = a(i, 1) v = Val(s) a(i, 1) = Format(v, String(10, "0")) & Mid(s & "z", 1 + Len(CStr(v))) Next i Application.ScreenUpdating = False With Range("A2").Resize(ubA, nc) .Columns(nc).Value = a .Sort Key1:=.Cells(1, nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal .Columns(nc).ClearContents End With Application.ScreenUpdating = True End Sub
