I'm having trouble with the sort method, but no clue as to why. Here is my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just want
to sort the range I am going to use to populate a listbox. If I select the
range then sort the selection it seems to work, but I didn't think I had to
select a range to be able to use the sort method. Any help?

Ben,

Excel likes to be told exactly what to do, and the parameter syntax needs to
be precise: you were missing the Order1 parameter, and your need a colon
after the key1, along these lines:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:=Sheets("Sheet1").Range("B1"), Order1:=xlAscending
End With

HTH,
Bernie
MS Excel MVP


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why. Here is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just
want
to sort the range I am going to use to populate a listbox. If I select
the
range then sort the selection it seems to work, but I didn't think I had
to
select a range to be able to use the sort method. Any help?

Excel isn't quite that demanding: (from help on Sort)

Order1 Optional XlSortOrder. The sort order for the field or range
specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending ***default***. Sorts Key1 in ascending order.

Emphasis (***) added.

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

Ben,

Excel likes to be told exactly what to do, and the parameter syntax needs
to
be precise: you were missing the Order1 parameter, and your need a colon
after the key1, along these lines:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:=Sheets("Sheet1").Range("B1"), Order1:=xlAscending
End With

HTH,
Bernie
MS Excel MVP


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why. Here is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just
want
to sort the range I am going to use to populate a listbox. If I select
the
range then sort the selection it seems to work, but I didn't think I had
to
select a range to be able to use the sort method. Any help?

You have a syntax error

With Sheets("Sheet1").Range("A1:B23")
.Sort key1:= Sheets("Sheet1").Range("B1")
End With

--
Regards,
Tom Ogilvy


"benb" wrote in message
...
I'm having trouble with the sort method, but no clue as to why. Here is
my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just
want
to sort the range I am going to use to populate a listbox. If I select
the
range then sort the selection it seems to work, but I didn't think I had
to
select a range to be able to use the sort method. Any help?

Range("A1:B23").Select

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

this has worked for me - however, i wasn't using the 'with'

don't know if this will work for your situation or not

J

"benb" wrote:

I'm having trouble with the sort method, but no clue as to why. Here is my
code:

With Sheets("Sheet1").Range("A1:B23")
.Sort key1 = Sheets("Sheet1").Range("B1")
End With

I've also tried just Sheets("Sheet1").Range("A1:B23").Sort but I'm
continually getting a Sort Method of Range Class Failed error. I just want
to sort the range I am going to use to populate a listbox. If I select the
range then sort the selection it seems to work, but I didn't think I had to
select a range to be able to use the sort method. Any help?