The following code is designed to sort an alphanumeric list consisting of
text & one or 2 digits by inserting a leading zero, sorting, and then
stripping the zero back, to produce the following results:

Pre-sort condition
--------------------
AC20
AC1
AC2
AC9
AC10

Normal Excel Sort Result
-----------------------------
AC1
AC10
AC2
AC20
AC9

Macro Sort Result
---------------------
AC1
AC2
AC9
AC10
AC20

The Selection.Sort line in the following code generates the error €œSort
method of Range class failed.€�

Does anyone know why?

Sub AlphaSort()
Dim rng As Range
Dim intFirst As Integer
Dim intLast As Integer

' Sorts Alphanumeric list by adding then removing leading zeros
Call AddLeadingZero

intFirst = Selection(1).Row
intLast = Selection(Selection.Count).Row
Set rng = Range(Cells(intFirst, "B"), Cells(intLast, "F"))
rng.Select

Selection.Sort 'Code fails here

Call RemoveLeadingZero

End Sub

Thank you.
Sprinks

A couple of things. you don't need the select. Since you have a range object
you can just sort it, but you have to specify a key. My guess would be by the
cell intfirst and column B, but that is up to you. Record a macro to see all
of the possible properties that you can set. Heading will probably be xlYes
and there may be a few other things you want to set.

rng.sort Key1:=???, ...
--
HTH...

Jim Thomlinson


"Sprinks" wrote:

The following code is designed to sort an alphanumeric list consisting of
text & one or 2 digits by inserting a leading zero, sorting, and then
stripping the zero back, to produce the following results:

Pre-sort condition
--------------------
AC20
AC1
AC2
AC9
AC10

Normal Excel Sort Result
-----------------------------
AC1
AC10
AC2
AC20
AC9

Macro Sort Result
---------------------
AC1
AC2
AC9
AC10
AC20

The Selection.Sort line in the following code generates the error €œSort
method of Range class failed.€�

Does anyone know why?

Sub AlphaSort()
Dim rng As Range
Dim intFirst As Integer
Dim intLast As Integer

' Sorts Alphanumeric list by adding then removing leading zeros
Call AddLeadingZero

intFirst = Selection(1).Row
intLast = Selection(Selection.Count).Row
Set rng = Range(Cells(intFirst, "B"), Cells(intLast, "F"))
rng.Select

Selection.Sort 'Code fails here

Call RemoveLeadingZero

End Sub

Thank you.
Sprinks

Thanks, Jim, it works flawlessly now.

Sprinks

"Jim Thomlinson" wrote:

A couple of things. you don't need the select. Since you have a range object
you can just sort it, but you have to specify a key. My guess would be by the
cell intfirst and column B, but that is up to you. Record a macro to see all
of the possible properties that you can set. Heading will probably be xlYes
and there may be a few other things you want to set.

rng.sort Key1:=???, ...
--
HTH...

Jim Thomlinson


"Sprinks" wrote:

The following code is designed to sort an alphanumeric list consisting of
text & one or 2 digits by inserting a leading zero, sorting, and then
stripping the zero back, to produce the following results:

Pre-sort condition
--------------------
AC20
AC1
AC2
AC9
AC10

Normal Excel Sort Result
-----------------------------
AC1
AC10
AC2
AC20
AC9

Macro Sort Result
---------------------
AC1
AC2
AC9
AC10
AC20

The Selection.Sort line in the following code generates the error €œSort
method of Range class failed.€�

Does anyone know why?

Sub AlphaSort()
Dim rng As Range
Dim intFirst As Integer
Dim intLast As Integer

' Sorts Alphanumeric list by adding then removing leading zeros
Call AddLeadingZero

intFirst = Selection(1).Row
intLast = Selection(Selection.Count).Row
Set rng = Range(Cells(intFirst, "B"), Cells(intLast, "F"))
rng.Select

Selection.Sort 'Code fails here

Call RemoveLeadingZero

End Sub

Thank you.
Sprinks