Return to cell after sort

[Geoff]

Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

 

[mangesh_yadav]

before starting the sort use
a = ActiveCell.address
after finishing sort use
range(a).select

- Manges

 

[Haldun Alay]

Hi,


before sorted
dim ActCell as Range
set ActCell=ActiveCell

after sorted
ActCell.Activate
set ActCell=nothing


--
Haldun Alay


"Geoff" <[email protected]>, iletide þunu yazdý Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

 

[Mike Fogleman]

Use a variable to store the ActiveCell contents, before the sort, then Find
it after the sort.

Sub Sort_Find()
Dim actvcell

Range("A16").Select 'change to your cell
actvcell = ActiveCell.Value 'store the cell value
' put your sort routine next
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Find the original cell value
Cells.Find(What:=actvcell, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False).Activate
End Sub

Mike F

 

[Tom Ogilvy]

The best approach is not to affect the activecell at all

Sub AASort()
With Worksheets("sheet1")
..Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
End Sub

won't affect the activecell event if it is in the range to be sorted or even
if it is on another worksheet in the workbook.

 

[Mike Fogleman]

My routine assumed you wanted to locate the original cell contents after the
sort has moved it. Mangesh & Haldun's routine will put you back to the
original cell location before the sort, no matter what the contents of that
cell is now. Choose whichever you needed.
Mike F

 

[Tom Ogilvy]

Disregard, I didn't see the "wherever it's moved to" in your question.

An adjustment for that might be:

Sub AASort()
vVal = ActiveCell.Value
With Worksheets("sheet1")
On Error Resume Next
set rng = Intersect(activeCell.EntireColumn, _
.Range("B9:Z30"))
On Error goto 0
.Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
if not rng is nothing then
rng.Find(vVal).Activate
End if
End Sub

This would assume that the value of the activecell is unique in that column.

 

[Tom Ogilvy]

With declarations:

Sub AASort()
Dim rng As Range, vVal As Variant
vVal = ActiveCell.Value
With Worksheets("sheet1")
On Error Resume Next
Set rng = Intersect(ActiveCell.EntireColumn, _
.Range("B9:Z30"))
On Error GoTo 0
.Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
If Not rng Is Nothing Then
rng.Find(vVal).Activate
End If
End Sub

 

[Geoff]

It's 1am here now, when I downloaded all these helpful replies.
Thanks fellas.
I'll give them a work through tomorrow and let you know how I get on.

Geoff

PS - You've hit the nail on the head. It was the "wherever it's moved
too" that was fogging me.

 

[Geoff]

Hi Mangesh,

Thanks for your reply.

I added "debug.print a" before and after the sort.
Range(a).Select returns me to the same cell, but not the same record.

I've learned from your reply that the address property returns a
string (eg $G$11).

So thanks again.
Regards
Geoff

 

[Geoff]

Hi Haldun,

Thanks for your reply.

With your solution, I'm returned to the same cell, but not the same
record.
After the sort, I want to be on the row for the person I was on before
the sort.

Thanks for taking time out to reply.
Regards
Geoff

 

[Geoff]

Hi Tom,

Thanks for your replies.
I'm almost home and dry.

I've used the Intersect method on a column that contains unique
values. I can unambiguously find the new row after the sort. I can
create a range object for the new location of the cell that was active
before the sort.

One problem remains. Do you (or anyone else) have any ideas please?
The show method of the new location cell's range object doesn't scroll
me to the cell, ie this code line doesn't work as I expect :

rngNewCell.Show

Thanks for your help.
Geoff

I don't suppose you'll need it, but this is how I ended up doing it:

' Ensure CaseNumber range name exists:
RetVal = RangeExists(mconCaseNumber)
If Not RetVal Then
RangeNameMissing_CannotSort mconCaseNumber
GoTo Bye
End If

' Ensure UsedArea range exists:
RetVal = RangeExists(mconUsedArea)
If Not RetVal Then
RangeNameMissing_CannotSort mconUsedArea
GoTo Bye
End If

' Turn off screen:
AllowScreenUpdates False

' 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:
ChangeFooter "Sorted by Case Number"

GoSub CleanUp

' Message user:
strMessage = "Now ordered by CASE NUMBER."
intOptions = vbInformation + vbOKOnly
strHeading = "Sort Complete"
MsgBox strMessage, intOptions, strHeading

Bye:

Exit Sub

 

[Geoff]

Hi Mike,

Thanks for your reply.

I have several command buttons on a customised toolbar to execute the
different sorts.

Some of the columns I'm sorting don't contain unique values. But I've
adapted your method and Tom's method and am almost there! (See reply
to Tom's post for outstanding issue.)

I've learned the Cells.Find method from your reply, so thanks for
that.

Regards.
Geoff

 

[Chip Pearson]

Geoff,

rngNewCell.Show

There is no Show method for a range object, so you should get an
"object doesn't support this method" error message. Instead, you
can use Application.Goto. E.g.,

Application.Goto rngNewCell, True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

also, I would expect

rngNewCell.Activate

by itself
to achieve what you want.

 

[Haldun Alay]

Hi,

A different solution to your problem.

if you are not using cell comments on your table, this works.




Dim Sht As Worksheet
Set Sht = ActiveCell.Parent
ActiveCell.AddComment
CmtCount = Sht.Comments.Count
ActiveCell.Comment.Text Text:="I was here"

....
....your sorting codes
....


Sht.Comments(CmtCount).Parent.Select
Sht.Comments(CmtCount).Delete




Kind Regards.
--
Haldun Alay

"Geoff" <[email protected]>, iletide þunu yazdý Hi Haldun,

Thanks for your reply.

With your solution, I'm returned to the same cell, but not the same
record.
After the sort, I want to be on the row for the person I was on before
the sort.

Thanks for taking time out to reply.
Regards
Geoff

 

[Geoff]

Hi Chip/Tom

Thanks for your replies.

The GoTo method scrolls me to the new location, so problem solved -
thanks for that, Chip. The Activate method doesn't scroll to the new
location, Tom - it just activates the cell but leaves it out of view.

My code compiled OK. I thought it would because the Access 2000 VB
Help system says:

"Show Method:
"Range object (Syntax 1): Scrolls through the contents of the active
window to move the range into view. The range must consist of a single
cell in the active document.
"Syntax 1
"expression.Show
"expression Required. For Syntax 1, an expression that returns an
object in the Applies To list."
(The 'Applies To List' includes the Range object.)

The above Help extract refers to a *cell in the active document*.

Could this refer to a Word document table cell, rather than an Excel
spreadsheet?

Something odd's going on - with code compiling but not working.

Thanks to you both.
Regards
Geoff.

 

[Geoff]

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

 

[Tom Ogilvy]

Sorry Geoff, I ran your code with just the
rngNewCell.Activate

(no rngNewCell.Show)
and it worked fine (as expected). It scrolled the window so the activated
cell was visible.

I ran it without the activate line; just the rngNewCell.show
and it did nothing. The original activated cell in its old location
remained selected.

Range does have a show method and as documented, I would expect it to do
what you intended.

Nonetheless, I am not sure how you are making activate alone to fail, but
for normal circumstances it works just fine.

 

[Geoff]

Thanks, Tom.
That's very helpful to know.

Strange. I'll check tomorrow (it's nearly 2am here now). I don't
think I was doing anything daft - indeed, if you ran my code and it
worked, that would seem to prove it. On my machine, Activate, Select
and Show all didn't scroll to the cell, whereas GoTo did. Perhaps
something else is inhibiting scrolling, but I can't think what. I'll
let you know how I get on. Thanks for confirming what I thought was
the case about the range object's show method.

Regards.
Geoff