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

[J.W. Aldridge]

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

 

[Bernie Deitrick]

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

 

[JLGWhiz]

Did you try ActiveCell?

 

[J.W. Aldridge]

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

 

[J.W. Aldridge]

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

 

[Bernie Deitrick]

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

 

[Dave Peterson]

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