Return to cell after sort

[Tom Ogilvy]

Here is the code I used:

I had random numbers in A1:J100
Column F had unique numbers
The active Cell was in column A (A10)
the last visible row was 28


After the sort, the value in A10 was in A63. A63 was visible and selected.
(in the middle of the visible vertical rows rather at the top of the screen
as in Application.Goto)


Sub Tester1()
mconCaseNumber = "F1"
mconUsedArea = "A1:J100"

' Get active cell's range, column and row numbers:
Set rngOldCell = ActiveCell
lngOldRow = rngOldCell.Row
lngOldCol = rngOldCell.Column

' Get Case Number column as range:
Set rngCaseNoCol = Intersect(Range(mconCaseNumber).EntireColumn,
Range(mconUsedArea))

' Test for intersection:
' If rngCaseNoCol Is Nothing Then GoTo InvalidCaseNumberIntersection

' Get Case Number column number:
lngCaseNoCol = rngCaseNoCol.Column

' Get Case Number for active row:
strCaseNo = Cells(lngOldRow, lngCaseNoCol).Value

' Execute sort on Case Number column:
Range(mconUsedArea).Sort _
Key1:=Range(mconCaseNumber), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

' Find old case number in Case Number column now it's sorted:
Set rngNewRow = rngCaseNoCol.Find(strCaseNo)

' Shouldn't happen, but test that case number is found:
' If rngNewRow Is Nothing Then GoTo CaseNumberNotFound

' Get new row number of cell that was active at start:
lngNewRow = rngNewRow.Row

' Get new range of cell that was active at start:
Set rngNewCell = Cells(lngNewRow, lngOldCol)

' Move to new cell location:
rngNewCell.Activate
' rngNewCell.Show ' PROBLEM WITH THIS LINE !

' Put sort order into footer:
Bye:

Exit Sub


End Sub

 

[Haldun Alay]

Hi Geoff,

If you are using Office XP or higher, you can use Watches class to locate your current cell before sort.

An example follows

Sub AddWatchAndSort()
Application.Watches.Delete
Application.Watches.Add Source:=ActiveCell
Call SortMyTable ' Your Sorting codes
Application.Watches.Item(0).Source.Activate
End Sub

Kind regards.
--
Haldun Alay
"Geoff" <[email protected]>, iletide þunu yazdý Hi Haldun

Thanks for this tip.

I thought I might tag the cell using a comment, but didn't know how to
do it.

For the current spreadsheet, each row has a unique value in one
column, so I've been able to use that to effect a solution (see reply
to Tom).

It's a pity there's not a bookmark property for a cell (as there might
be for a record in a recordset) to enable fast return to the same
location.

Thanks again.
Regards
Geoff

 

[Geoff]

Hi Tom,

Well, I got to the bottom of it eventually.

As you know, I was wrong to say that the Activate, Select and Show
methods don't scroll the screen - as your test demonstrated. My
guess, after getting your post, proved right - that something else was
inhibiting scrolling.

There was a slight difference in our coding.

Your code began just after my code turned off screen updating with:
AllowScreenUpdates False
which called:
Private Sub AllowScreenUpdates(blnYes As Boolean)
Application.ScreenUpdating = blnYes
End Sub

It appears that, if:
Application.ScreenUpdating = False
when the Activate, Select, or Show methods are called, then the active
cell doesn't end up in the middle of the screen (but somewhere off
screen). However, the GoTo method does scroll the screen while screen
updating is off.

Having the active cell in the middle of the screen (after Activate,
Select, or Show) is better for the user. But the penalty is the
screen flashes a little more while the screen scrolls.

Having the active cell at the top (after GoTo) is not so good for the
user. But there's less flash.

I decided to use Activate.

Thanks for your help, Tom. It's been the best I've received from
anyone here or any other group. You should be an MVP (if you're not
already).

Geoff

PS - I replicated your test spreadsheet. Neat!

 

[Geoff]

Hi Haldun,

Nice to know that Microsoft have already responded to what must have
been a general need on this issue.
I have Office XP Developer, but not yet upgraded!

Thanks for your interest.
Geoff

 

[Geoff]

I'm posting this again as it didn't download first time :-

Hi Tom,

Well, I got to the bottom of it eventually.

As you know, I was wrong to say that the Activate, Select and Show
methods don't scroll the screen - as your test demonstrated. My
guess, after getting your post, proved right - that something else was
inhibiting scrolling.

There was a slight difference in our coding.

Your code began just after my code turned off screen updating with:
AllowScreenUpdates False
which called:
Private Sub AllowScreenUpdates(blnYes As Boolean)
Application.ScreenUpdating = blnYes
End Sub

It appears that, if:
Application.ScreenUpdating = False
when the Activate, Select, or Show methods are called, then the active
cell doesn't end up in the middle of the screen (but somewhere off
screen). However, the GoTo method does scroll the screen while screen
updating is off.

Having the active cell in the middle of the screen (after Activate,
Select, or Show) is better for the user. But the penalty is the
screen flashes a little more while the screen scrolls.

Having the active cell at the top (after GoTo) is not so good for the
user. But there's less flash.

I decided to use Activate.

Thanks for your help, Tom. It's been the best I've received from
anyone here or any other group. You should be an MVP (if you're not
already).

Geoff

PS - I replicated your test spreadsheet. Neat!

 

[Geoff]

Hi Tom,

Well, I got to the bottom of it eventually.

As you know, I was wrong to say that the Activate, Select and Show
methods don't scroll the screen - as your test demonstrated. My
guess, after getting your post, proved right - that something else was
inhibiting scrolling.

There was a slight difference in our coding.

Your code began just after my code turned off screen updating with:
AllowScreenUpdates False
which called:
Private Sub AllowScreenUpdates(blnYes As Boolean)
Application.ScreenUpdating = blnYes
End Sub

It appears that, if:
Application.ScreenUpdating = False
when the Activate, Select, or Show methods are called, then the active
cell doesn't end up in the middle of the screen (but somewhere off
screen). However, the GoTo method does scroll the screen while screen
updating is off.

Having the active cell in the middle of the screen (after Activate,
Select, or Show) is better for the user. But the penalty is the
screen flashes a little more while the screen scrolls.

Having the active cell at the top (after GoTo) is not so good for the
user. But there's less flash.

I decided to use Activate.

Thanks for your help, Tom. It's been the best I've received from
anyone here or any other group. You should be an MVP (if you're not
already).

Geoff

PS - I replicated your test spreadsheet. Neat!

 

[Tom Ogilvy]

thanks for the update. Glad you find some of the information useful (if
sometimes hard to read).