Active cell in sorting

  • Thread starter Thread starter Znake
  • Start date Start date
Z

Znake

In a function i'm using "activecell.row", and that works fine, but
when sorting the value is calculated for each row sorted and when
using the activecell.row the active cell is (i think) the upperleft
cell of the record-area which is to be sorted so the value of
activecell.row is used for the other rows when sorting.

SO:
when row 4 and 5 is sorted, when using the function in row 5 the
activecell.row is 4 and not 5.

Does any one now how to fix that problem. (is there a "currentrow or
something when sorting?)
Thanks Anders
 
Anders,

I am not sure I fully follow your thoughts, but activecell is not always the
top-left of a selection, it is the cell the cursor is in. To maybe
understand what I mean, put the cursor in D28, and then select up to B10
say. You will see that D28 is still white, indicating the activecell.
Similarly, select B20:B28, then Ctrl-Select C28-E27 then put then
Ctrl-Select C26:E20, and you will see that C26 is white, and active.

I am not sure if this helps. How is the sort area being chosen/selected in
your code?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
Anders,

I am not sure I fully follow your thoughts, but activecell is not always the
top-left of a selection, it is the cell the cursor is in. To maybe
understand what I mean, put the cursor in D28, and then select up to B10
say. You will see that D28 is still white, indicating the activecell.
Similarly, select B20:B28, then Ctrl-Select C28-E27 then put then
Ctrl-Select C26:E20, and you will see that C26 is white, and active.

I am not sure if this helps. How is the sort area being chosen/selected in
your code?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...

I will try to show another example:
The following data are to be sorted:
a (active)
f (finished)
w (waiting)

The result should be
a
w
f

How can that be done in excel?
Anders
 
Hi Anders,
I would suggest that you use numbers for sorting.

You can accomplish this with a helper column and
sort on the helper column. (not case sensitive)
=MATCH(B1,{"a";"w";"f"},0)

As far as the comments about the active cell always
being in the upper left of a selection or a range that is
not true.
1) Scroll down and to the right so that cell C3 is in the
upper left corner. Then Select columns C:E or
using the name box select columns B:D then
scroll left and up so that A1 is in the upper left corner.
2) with A1 in upper left corner - just so you can see results
Select cell F4, then Ctrl+A

You can preselect a Cell, then use Ctrl+A to use those
Ascending and Descending sort icon buttons without
messing up your data when you have empty columns.
More information in
http://www.mvps.org/dmcritchie/excel/sorting.htm
 
David McRitchie said:
Hi Anders,
I would suggest that you use numbers for sorting.

You can accomplish this with a helper column and
sort on the helper column. (not case sensitive)
=MATCH(B1,{"a";"w";"f"},0)

....
Click to expand...

Thanks a lot
That was the solution.
Anders
 
Back
Top