Sort Error

[Guest]

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

 

[Guest]

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:=???, ...

 

[Guest]

Thanks, Jim, it works flawlessly now.

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:=???, ...