Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Retain cell selection through sort

  Asked By: Mona    Date: Mar 02    Category: MS Office    Views: 1191
  

I don't see anything in earlier posts suggesting this.

I have a spreadsheet with weeks along the top and projects and
resources (equipment and people) along the side. Long ago I set up
buttons to run macros that sort on people or projects or equipment to
group like things together to visualize loading and over booking. At
the time, I did mostly macro recording, but figured out some things
as well. After sorting, I just selected a cell at the top.
This, however, is awkward. When deep in the sheet, the sort throws
you to the top left and you must scroll around for data (project and
week) you were fiddling with.
I would like to have the *data item* that had been selected before
the sort be the same one selected after the sort. I mean I want the
selection to "follow" the sort. If I have a cell selected with
John's name in it in the row for project X, I want that "John" cell
to still be selected wherever it winds up (above or below the
original location).

Therefore, if cell C20 is selected before the sort, it probably won't
be after – since that cell may have been sorted elsewhere.

One idea is to stuff some unique data in a cell way off to the right
in the row where you start; then, after the sort, search for the
weird data, delete it and return to the desired column...if I knew
how to do that.

Share: 

 

10 Answers Found

 
Answer #1    Answered By: Mark R     Answered On: Mar 02

If I understand correctly... the following might work.

Dim StartAddress$

StartAddress$ = Activecell.Address

"do your code here"

Range(StartAddress$).Select

 
Answer #2    Answered By: Jaime Bowman     Answered On: Mar 02

Your method retains selection  of an absolute cell
location. I want the selection to follow the *contents* not stay at
the same the location.

What I want:
If the *contents* of cell  C20 is moved, by a sort, to location  C40, I
want cell C40 to be selected  after the sort.

I'm still thinkin' I can save a "bread crumb" (some unique  data)in
any unused cell in the same row. The column  doessn't change in the
sort. Then, after the sort, search  for the bread crumb to find the
moved row... All I need to learn is how to search a column in a
macro. Sounds slow, but effective.

Thanks anyway, it may come in usefull elsewhere.

 
Answer #3    Answered By: Brandon Tucker     Answered On: Mar 02

Is the contents of your cell  repeated anywhere?

If not, you might try:

Sub FindReplace()
Dim strValue As String

strValue = ActiveCell.Value

'Perform your Sort commands

Columns("c:c").Select
Cells.Find(What:=strValue, After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

 
Answer #4    Answered By: Al Allen     Answered On: Mar 02

It can be... No telling what cell  is selected  and many have the
same person's name or resource name or activity designator.

HOWEVER... I could save the real contents of the selected cell and
replace it with some unused garbage (like Z43!qq7) Then, after the
sort, search  for the garbage and replace the data  and re-select...
Then I can use your find...
Perhaps a bit easier that placing the garbage in some far right
cell...

 
Answer #5    Answered By: Viola Hanson     Answered On: Mar 02

this didn't QUITE do it since the content is non unique,
but gave me the key (pun) for my solution: Stuff unique data  into the
selected cell  and search  for that. Like so:

Sub Sort_n_Select()
'
' Sort_n_Select Macro
macro  recorded (and edited) 10/21/04 by Steve Noskowicz
'
' This macro has the selected  cell follow along with a sort.
'
Dim SelectedCellContents As Variant
'
Application.ScreenUpdating = False ' Suspend screen updating

SelectedCellContents = ActiveCell.Value ' Hold the selected
cell's contents
SelectedColumn = ActiveCell.Column ' Note the selected cell's
column
ActiveCell.Value = "ZUJQRX" ' Insert a search key in the selected
cell. If you're a ham and Navy, you'll recognize this key (:-)
'
'=========== Example sort  on col D to move the selected cell.======
Rows("5:13").Select ' Select the database
Selection.Sort Key1:=Range("D5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'================================================= end sort =======
'
Columns(SelectedColumn).Select ' Select the "Key" Column
'
' Now find the Keyed cell
Selection.Find(What:="ZUJQRX", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True).Activate

ActiveCell.Select 'Select the found cell

ActiveCell.FormulaR1C1 = SelectedCellContents ' Restore the
content.

Application.ScreenUpdating = True 'Resume screen updating

End Sub

=================
AND it doesn't error out if the selected cell isn't within the sorted
area.

 
Answer #6    Answered By: Arthur Cole     Answered On: Mar 02

I just realzed this has a problem if the selected cell  is in
the sort  column since it would then sort on the key... Will fix...
probably by moving the selected  cell right or left  out of the sort
column since the row  and nearby cells are what I want to keep in view.
Steve

Why is it that you always see something within a microsecond AFTER
hitting SEND??

 
Answer #7    Answered By: Jim Williamson     Answered On: Mar 02

Have you added interaction such as an Input Box to pick the column  to
sort on?

 
Answer #8    Answered By: Sherri Parker     Answered On: Mar 02

If I understand "Input Box"(like a MsgBox) no. This is too
cumbersome.

I simply put text boxes (could have used buttons also) at the top  of
each collumn to be sorted and coupled each to the respective (fixed
collumn) sort  macro. Just click on the text box and zing! sorted on
that collumn. Been working for several years.

HOWEVER... Though I have my "Selection follows Sort" working, I have
another problem which I can't solve.
NO problems *without* split panes, however...

When I manually split the window into two panes (grab the lower right
mini-bar) and make two panes (left and right sides), I have a cell
selection problem.

If I manually select a cell  in the right pane then run  the sort
macro, the macro  thinks I have a left  pane cell selected! (but
handles it correctly) What I figured out is that the macro "gets"
the last cell selected  in the *LEFT* pane, instead of the right-hand-
cell which _IS_ selected when the macro is entered.

Perhaps I just don't know how to specify the selected cell (in the
macro) .... Help, of course, wasn't any.

Here's my test macro, but the problem has to be obvious above
the '+++++++++++++++++++
====================================================
Sub Sort_N_Select()
'
' Sort_N_Select Macro
' Macro recorded (and edited) 10/21/04 by Steve Noskowicz
'
' This macro has the selected cell follow along with a sort.
'
Dim SelectedCellContents As Variant
InSortCol = 0 'A "selection is in sort col" Flag for fixing things
later...
'
SortColumn = 4 ' Col D sort routine
Application.ScreenUpdating = False ' Suspend screen updating
'
ActiveCell.Activate ' These three don't enlighten the macro.
ActiveCell.Cells.Activate
ActiveCell.Cells.Select

Debug.Print "Enter, Col D Sort ==========="
Debug.Print " ActiveCell="; ActiveCell ' this is the
contents '.Value' optional
Debug.Print " ActiveCell.Row="; ActiveCell.Row ' the row?
Debug.Print " ActiveCell.column "; ActiveCell.Column ' the Column?
' The three variables above this line all show the last selected
_Left_ pane selection
' rather than the _current_ (upon macro entry) right pane
selection. Arrrgg! (:-(
' Therefore, the remaining cell references are in error.

'++++++++++++++++++++++++++++++++++++
If ActiveCell.Column = SortColumn Then
InSortCol = 1 ' In the sort colume
Debug.Print " You're in the sort column=";
ActiveCell.Column ' is a digit
'select one cell to the right first.
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
Debug.Print " Move to the right, to Col ";
ActiveCell.Column
ActiveCell.Select 'Select the new. Not sure if it is needed,
try W/O
End If
'
SelectedCellContents = ActiveCell.Value ' Hold the selected
cell's contents
Debug.Print " Save contents < "; SelectedCellContents
SelectedColumn = ActiveCell.Column ' Note the selected cell's
column
Debug.Print " Key column  noted = "; SelectedColumn
ActiveCell.Value = "ZUJQRX" ' Insert a search  key in the selected
cell
Debug.Print " Insert Key > "; ActiveCell.Value

'Show the key substitution
Application.ScreenUpdating = True
For A = 0 To 100000
For B = 0 To 500: Next
Next
Application.ScreenUpdating = False

Debug.Print " Do Sort..."
'Do your col D sort (and move the selected cell).
Rows("5:13").Select ' Select the database
Selection.Sort Key1:=Range("D5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Debug.Print " ...Sort done."
Columns(SelectedColumn).Select ' Select the "Key" Column
Debug.Print " Select the Key's Column = "; SelectedColumn

' Now find the Keyed cell
Selection.Find(What:="ZUJQRX", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True).Activate
Debug.Print " Key Found & cell selected"


'Show the key'd cell After the sort
ActiveCell.Select 'Select the new. Not sure if it is needed,
try W/O
Application.ScreenUpdating = True
For A = 0 To 100000
For B = 0 To 300: Next
Next
Application.ScreenUpdating = False

ActiveCell.FormulaR1C1 = SelectedCellContents ' Restore the
contents.
Debug.Print " Replace contents >>"; SelectedCellContents
'
If InSortCol = 1 Then ' If you WERE in the sort column
'' BUT THis doesn't do it IF you start  in column E=5 !!


Debug.Print " You're right of the sort column=";
ActiveCell.Column ' is a digit
'Go back one cell to the left.
ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate
Debug.Print " Offset it back to the left, to Col ";
ActiveCell.Column
End If
ActiveCell.Select 'Select the new. Not sure if it is needed,
try W/O
Debug.Print " It doesn't clear the column selection  if I
don't 'Select'."
Application.ScreenUpdating = True

Debug.Print "Exit, Col D Sort ==========="
End Sub
Sub Restore()
'
' Restore Macro
' Macro recorded 10/21/04 by snoskow1
'
'Resets the database to the original order.
'
Dim SelectedCellContents As Variant
'
Application.ScreenUpdating = False ' Suspend screen updating

SelectedCellContents = ActiveCell.Value ' Hold the selected
cell's contents
SelectedColumn = ActiveCell.Column ' Note the selected cell's
column
ActiveCell.Value = "ZUJQRX" ' Insert a search key in the selected
cell


Rows("5:13").Select
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E17").Select

Columns(SelectedColumn).Select ' Select the "Key" Column

' Now find the Keyed cell
Selection.Find(What:="ZUJQRX", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True).Activate

ActiveCell.Select 'Select the found cell

ActiveCell.FormulaR1C1 = SelectedCellContents ' Restore a
contents.

Application.ScreenUpdating = True
End Sub

 
Answer #9    Answered By: Rachel Barnes     Answered On: Mar 02

The 'interaction' that Steve is referring to sounds like he used AutoFilter
where it automatically puts little drop down boxes in the header of a table so
the user can select what criteria to sort  the table on. This can be found in the
menu Data/Filter/AutoFilter.

Dawn's "InputBox" is a VBA method for the Application object that queries the
user for a specific type of data  (string, cell  address, etc.) It's not clear
whether Dawn means "inputbox" in the generic sense (like any text field) or the
one from the Application object (only done via VBA code) which has multiple
arguments (only one required).

Public Sub makeBold()
Dim rng As Range

' Type:=8 refers to a cell/range
Set rng = Application.InputBox("Select a cell/s", Type:=8)
rng.Font.Bold = True
Set rng = Nothing
End Sub

 
Answer #10    Answered By: Julio Morgan     Answered On: Mar 02

I am not using the auto filter on this sheet,
nor "Input Bozes". In Excel, you can draw things  using the draw
tools (autoshapes, lines , arrows, "Text Boxes", etc). There is
usually a button dedicated to text boxes at the bottom of the Excel
window if you have Draw Tools shown. One looks like a small page
with an "A" and some lines. With this you draw a box and type some
text into it, color it, border it, etc. I made text boxes at the top
of the columns which I wanted sorted and then linked the text boxes
to the respective macros....However, I believe this is unrelated to
my current problem.

With only one pane in the window, I get correct cell  references into
my macro  and all works as expected.

When I split the window into two panes (L/R), my "ActiveCell"
references do not return  the correct cell reference (location). I
only get the last selected  cell in the *left pane* even if a cell in
the right pane *IS* selected (when the macro is entered).
Two more symptoms:
1- If I select a left  pane cell and "arrow" to the right (selecting
cells to the right), moving the selected cell into the area currently
covered by the right pane (also causing the left pane to
automatically scroll  to the right - you now see the selected cell in
poth panes since they cover common areas), THEN my macro gets the
correct cell reference.
2- If I "Freeze" the window panes, then I appear to always get the
correct cell referenced into the macro - regardless whether it is in
the left or right pane.
I see a "Panes" object or method (I can't tell the difference just
yet) AND have been able to write a statement which returns a value
(in the immediate window via a Debug.Pring statement) showing how
many panes are open (1, 2, or 4), but do not know what I need to do
(other than freezing the window) to always get a correct cell
reference regardless of which pane it happens to be visible in.

 
Didn't find what you were looking for? Find more on Retain cell selection through sort Or get search suggestion and latest updates.




Tagged: