sort method
benb
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?
@consumerdotorg Bernie Deitrick
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" <be...@discussions.microsoft.com> wrote in message
news:C14709E8-C7BC-4B0D...@microsoft.com...
Tom Ogilvy
With Sheets("Sheet1").Range("A1:B23")
.Sort key1:= Sheets("Sheet1").Range("B1")
End With
--
Regards,
Tom Ogilvy
"benb" <be...@discussions.microsoft.com> wrote in message
news:C14709E8-C7BC-4B0D...@microsoft.com...
Gixxer_J_97
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
Tom Ogilvy
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
news:%23w6NxX3...@TK2MSFTNGP10.phx.gbl...
@consumerdotorg Bernie Deitrick
Normally, I would agree, but I have no faith in Excel Help's truthfulness,
especially due to the persistence of user selected parameters.
If you sort a range descending once, and go to re-sort that range, Excel
will remember and by default choose descending. And the same is true with
code: Yes, that parameter is optional, but if you want to sort ascending,
and the range had been sorted descending sometime previously, then you had
better use the Order parameter, 'default' or not, because Excel won't
default to ascending.
I probably should have included all the other parameters, since they also
have the same drawbacks, but I couldn't remember their names off the top of
my head. I know that I've also had problems with headers:, and
orientation(?) - time to break out the macro recorder..... :-)
Bernie
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:%237ObLk3...@tk2msftngp13.phx.gbl...
Tom Ogilvy
--
Regards,
Tom Ogilvy
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uzaeFu3O...@tk2msftngp13.phx.gbl...
Tom Ogilvy
-----------------------------
Remarks
The settings for Header, Order1, Order2, Order3, OrderCustom, and
Orientation are saved, for the particular worksheet, each time you use this
method. If you don't specify values for these arguments the next time you
call the method, the saved values are used. Set these arguments explicitly
each time you use Sort method, if you choose not to use the saved values.
-----------------------------
Nonetheless, and while your advice if very sound, the cause of the users'
code failure is not the omission of Order:=xlAscending
but the failure to use the colon equal combination with Key1 as we both
pointed out
--
Regards,
Tom Ogilvy
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:eWD3a03O...@TK2MSFTNGP15.phx.gbl...
@consumerdotorg Bernie Deitrick
moon? ;-)
Bernie
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:uIopnC4O...@TK2MSFTNGP10.phx.gbl...