MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorting Macro - HELP!


Posted by Scott Machtin on May 17, 2001 7:37 AM

Hi, I am looking to have a sorting macro that sorts based on the last two characters of the field. Is this possible? Like

AZY
BYZ
ZAA
ABB

would be sorted to

ZAA
ABB
BYZ
AZY


Posted by Benedick Dogberry on May 17, 2001 8:00 AM

Scott
Check this :-

Sub Sort_Last2()
Dim data As Range
Set data = Range(Range("A1"), Range("A65536").End(xlUp))
Application.ScreenUpdating = False
With data
.EntireColumn.Insert
.Offset(0, -1).FormulaR1C1 = "=RIGHT(RC[1],2)"
.EntireRow.Sort Key1:=data(1, 1).Offset(0, -1), Key2:=data(1, 1)
.Offset(0, -1).EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub

BD


Posted by Scott Machtin on May 18, 2001 2:50 PM

Worked Perfectly!!! Thanks!!