Sort By Column A When Letter On End

[Dazzawm]

I have column A with data of varying lengths. I need a macro please that will sort the entire sheet by column A when there is a letter on the end like below first. I have highlighted for clarity once sort is done. Thanks

Before

AUA316D 5005A
AUA316D 5005B
AUA320D 1032
AUA320D 1034
AUA320D 2001B
AUA320D 2030
AUA320D 2034

After

AUA316D 5005A
AUA316D 5005B
AUA320D 2001B
AUA320D 1032
AUA320D 1034
AUA320D 2030
AUA320D 2034

 

[Dazzawm]

Try this:

Sub try_1()
Dim i As Long, n As Long
Dim va, vb
Dim xHelp As String

xHelp = "C"  'helper column
n = Range("A" & Rows.Count).End(xlUp).Row
Columns(xHelp).Clear
va = Range("A1:A" & n)
ReDim vb(1 To n, 1 To 1)

For i = 1 To UBound(va, 1)
    If Right(va(i, 1), 1) Like "[A-Z]" Then vb(i, 1) = 1
Next

Cells(1, xHelp).Resize(n, 1) = vb
Range(Cells(1, "A"), Cells(n, xHelp)).Sort Key1:=Cells(1, xHelp), Order1:=xlAscending, Header:=xlYes
Columns(xHelp).Clear
End Sub

That works, much obliged squire.

 

[Joe4]

What do you do when it falls so far down the list and you still haven't got a solution?

If you go 24 hours without any new replies, you can "bump" the thread back to the top by replying to it yourself again.