Cursor Position After Sort Operation

[Jeff W.]

I have a work log I am using in Excel, and have a custom feature that
will sort by status which it gets from the first column.

The cursor postion end up at A1 "top of the page"


Below, is what I have right now and I think the "Range("A1").Select"
should be changed but I dont how to make it position on the first cell
of the last item in the column that has contents

' sort by status
Sheets("Activity").Select
Range("A1:H65536").Select
Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub


Can someone help me change it to do this, please?

Thanks,

Jeff W.

 

[Jim Thomlinson]

So you want the cursor to end up in the first blank cell in column A??? If so
then...

Sheets("Activity").Range("A1:H65536").Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

Note that I changed the Header option in your sort from xlGuess to xlYes.
You are better off to specify if you have a header than to let XL make it's
best guess.

 

[Jim Cone]

'sort by status
Sheets("Activity").Select
Range("A1:H65536").Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
'Range("A1").Select
Cells(Rows.Count, 1).End(xlUp).Select
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - Free trial download for "Special Sort")



"Jeff W."
wrote in message
I have a work log I am using in Excel, and have a custom feature that
will sort by status which it gets from the first column.
The cursor postion end up at A1 "top of the page"

Below, is what I have right now and I think the "Range("A1").Select"
should be changed but I dont how to make it position on the first cell
of the last item in the column that has contents

' sort by status
Sheets("Activity").Select
Range("A1:H65536").Select
Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub

Can someone help me change it to do this, please?
Thanks,
Jeff W.

 

[Jim Thomlinson]

Oops... My code does not select the sheet you specify so the sort will work
but it may not work out correctly in terms of selecting the correct cell on
the correct sheet. Try this...

Sheets("Activity").Range("A1:H65536").Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Sheets("Activity").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

 

[Jeff W.]

Excellent!

Thank you Jim...

<Jeff>