Cursor Position After Sort Operation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff W. - 28 Jan 2008 17:15 GMT

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.

Reply to this Message

Jim Thomlinson - 28 Jan 2008 17:58 GMT

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.

Signature

HTH...

Jim Thomlinson

> 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.
[quoted text clipped - 20 lines]
>
> Jeff W.

Reply to this Message

Jim Thomlinson - 28 Jan 2008 18:12 GMT

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

Signature

HTH...

Jim Thomlinson

> So you want the cursor to end up in the first blank cell in column A??? If so
> then...
[quoted text clipped - 35 lines]
> >
> > Jeff W.

Reply to this Message

Jim Cone - 28 Jan 2008 17:59 GMT

'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

Signature

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.

Reply to this Message

Jeff W. - 28 Jan 2008 18:18 GMT

Excellent!

Thank you Jim...

<Jeff>

> 'sort by status
> Sheets("Activity").Select
[quoted text clipped - 3 lines]
> 'Range("A1").Select
> Cells(Rows.Count, 1).End(xlUp).Select

Reply to this Message