How to leave Selection.Sort Key open....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 18 Aug 2008 16:33 GMT

Any way to change the sort key to be wherever cell it lands on in
column C?

Not limited to row number 73,74.....

Sub Macro1()

LastRowColA = Range("c65536").End(xlUp).Row

Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:="R74C3", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlLeftToRight
End Sub

Reply to this Message

Bernie Deitrick - 18 Aug 2008 16:48 GMT

Jeremy,

Sub Macro2()
ActiveCell.CurrentRegion.Sort _
Key1:=Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireRow).Cells(1, 1), _
Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End Sub

HTH,
Bernie
MS Excel MVP

> Any way to change the sort key to be wherever cell it lands on in
> column C?
[quoted text clipped - 10 lines]
> OrderCustom:=1, Orientation:=xlLeftToRight
> End Sub

Reply to this Message

J.W. Aldridge - 18 Aug 2008 17:02 GMT

Didn't quite work....
Error message on
DataOption1:=xlSortNormal

Also, I still need it to use the last row used in the sheet, starting
with column C.

Thanx

Reply to this Message

J.W. Aldridge - 18 Aug 2008 17:17 GMT

Added the search code, but still......

Error on "DataOption1:=xlSortNormal".

Sub Macro99()

LastRowColA = Range("c65536").End(xlUp).Row

ActiveCell.CurrentRegion.Sort _
Key1:=Intersect(ActiveCell.CurrentRegion,
ActiveCell.EntireRow).Cells(1, 1), _
Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End Sub

Reply to this Message

Bernie Deitrick - 18 Aug 2008 17:43 GMT

This works for me, always sorting based on the last row of data:

Sub Macro99B()
Dim myC As Range

Set myC = Range("C65536").End(xlUp)
myC.CurrentRegion.Sort _
Key1:=myC, _
Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End Sub

Signature

HTH,
Bernie
MS Excel MVP

> Added the search code, but still......
>
[quoted text clipped - 11 lines]
> DataOption1:=xlSortNormal
> End Sub

Reply to this Message

Dave Peterson - 18 Aug 2008 18:33 GMT

DataOption# was added in xl2002 (IIRC). Just delete that portion from your
code.

> Didn't quite work....
> Error message on
[quoted text clipped - 4 lines]
>
> Thanx

Signature

Dave Peterson

Reply to this Message

JLGWhiz - 18 Aug 2008 16:56 GMT

Did you try ActiveCell?

> Any way to change the sort key to be wherever cell it lands on in
> column C?
[quoted text clipped - 10 lines]
> OrderCustom:=1, Orientation:=xlLeftToRight
> End Sub

Reply to this Message