Referring to a Range Based on Selected Rows

[Guest]

To sort a list such as the following alphanumerically, I've created
subroutines to insert a leading zero on the single-digit entries, sort, and
then strip off the leading zero. These work fine, assuming the user has
selected the entire range to be sorted, and not just entries in this column.

I'd like to bulletproof the macro by:
- determining the beginning and ending row of the selection (x, y)
- sorting the range Bx:Fy

Thanks for your input.

Sprinks

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

 

[Tom Ogilvy]

x = selection(1).row
y = selection(selection.count).row
set rng = Range(cells(x,"B"),cells(y,"F"))

 

[Guest]

Thanks, Tom; this works.

I'm having an additional problem. The Selection.Sort line in the following
code generates the error “Sort method of Range class failed.â€

Can you tell me 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

Call RemoveLeadingZero

End Sub

Thank you.