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

 

[Joe4]

That's not allowed I don't think.

That is correct.

James, please do not recommend that, as per rule 12 here: Message Board Rules

 

[Dazzawm]

That is correct.

James, please do not recommend that, as per rule 12 here: Message Board Rules

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

 

[Akuini]

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

 

[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

Thanks I will try shortly. I noticed it says helper column in C but I have data in C along with the rest of the sheet?

 

[Akuini]

Thanks I will try shortly. I noticed it says helper column in C but I have data in C along with the rest of the sheet?

Just change the temporary helper column to suit in this part:

xHelp = "C"  'helper column

 

[Dazzawm]

Just change the temporary helper column to suit in this part:

xHelp = "C"  'helper column

And it will sort the entire used sheet by column A first?

 

[Akuini]

And it will sort the entire used sheet by column A first?

No, it will sort the range from A to helper column by the helper column.

 

[Dazzawm]

No, it will sort the range from A to helper column by the helper column.

As I said in initial post I need the whole sheet sorted by column A first with cells with letters on the end in A first.

 

[Dazzawm]

If I put the helper column after the last column of data will that do the sort for whole sheet?

 

[Akuini]

If I put the helper column after the last column of data will that do the sort for whole sheet?

Yes