data sort defaults

T

thebaldsoprano

On a shared doccuments why is it that sometimes excel always recognizes my
data range has header rows and other times i've got to tell it i do
everytime. every doccument has a clearly defined header row. what am i
missing here?

thanks in advance for your knowledge.
 
D

Dave Peterson

I don't think it has anything to do with the "sharedness" of the file.

And what constitutes a "clearly defined header row" to you ain't necessarily the
same way that excel looks at it.

Sometimes changing the font/boldness of the header will be enough.

But as a rule, I don't let excel guess at my range to sort--or if it has a
header. I think it's just to dangerous.
 
J

JoeSpareBedroom

On a sheet I need to sort constantly, I recorded a macro to select a named
range, like DataArea, and then sort using the parameters I put in place
during the recording (ignore header row, for instance). Works fine. Just one
thing: The named range has to include all my occupied cells. If I add rows
past the bottom of the named range, I have to redefine the range, then run
the macro.
 
D

Dave Peterson

If you can pick out a column in that range that always has data in it, you could
use a dynamic range name that grows and contracts with your data.

Debra Dalgleish explains it here:
http://contextures.com/xlNames01.html#Dynamic

Other alternatives...

In xl2003, you can apply data|filter|autofilter to the range and use the
dropdown arrow to sort your data.

Debra Dalgleish has a technique at her site that adds invisible rectangles in
the headers and then sorts the data by that field when you click on one of those
rectangles.

http://contextures.com/xlSort02.html
 
J

JoeSpareBedroom

Doing 218 things at once right now, but here's a little bit of Excel 4.0 (!)
macro code that always selected the correct range, regardless of whether it
shrank or grew since the last visit. Maybe someone can update this to VBA:

=SELECT.LAST.CELL()
=SELECT(ACTIVE.CELL():!$A$1)
 
D

Dave Peterson

I wouldn't bother.

There are lots of ways built into VBA that can find the last used cell--no
matter how you define it.
Doing 218 things at once right now, but here's a little bit of Excel 4.0 (!)
macro code that always selected the correct range, regardless of whether it
shrank or grew since the last visit. Maybe someone can update this to VBA:

=SELECT.LAST.CELL()
=SELECT(ACTIVE.CELL():!$A$1)
Click to expand...
 
Top