Locking header row for "data sort" 7

[FYRGUY47]

Greetings:
Using Excel 2003, I have a spreadsheet that I need to sort and keep the header row in place. I typically do not have a problem keeping the header row in place during the sort, however at times I have received sheets from different people that I can't isolate the header row during the sort. I have gone into the sort data options and clicked the box that says "has header row" on all occasions, but it still sorts the header.

Any ideas would be helpfull... Thank you

Chris

 

[Gavona]

If you explicitly define the range to be sorted then you should have no problem. Highlight the desired range before Data,Sort..... Often You can do this by selecting the top left cell then Shift-Ctrl-End to extend to the bottom right cell.

It sounds like you are not doing this so Excel guesses at the range. To help Excel guess correctly you should follow these design principles (they also make many other things work better - such as Autofilter, pivot tables).

* Ensure that there is a blank row ABOVE the first row of your table
* Ensure that your table has contiguous data, ie no blank rows or columns within your table.
* This gives a fuller descrption of best practice in table design faq68-5184


Gavin

 

[FYRGUY47]

Gavin,

Thank you...! I typically just select any one cell in the column and use the sort button on the top of the excel tool bar to sort a particular column.

I inserted a empty row below the header and it worked perfectly. Thanks again! That one had been driving me crazy

Chris

 

[SkipVought]

I inserted a empty row below the header and it worked perfectly.

That is a VERY BAD PRACTICE!. There should be NO EMPTY ROW anywhere in your table.

There is a little known feature that you can put in your toolbar. It selects the cells contiguous to the active cell. RIght-click the toolbar and select Customize - Commands Tab: Edit Category -- Scroll to the bottom of the Commands and drag the [red]Select Current Region[/red] button to an existing toolbar.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Skip: In 2003 and later, isn't that the same thing as pressing [Ctrl]+[A] once? (If pressed again, all cells are selected)

I have gone into the sort data options and clicked the box that says "has header row" on all occasions, but it still sorts the header.

I've never seen that behavior. Do you mean that, instead of pressing one of the 'quick sort' buttons (labeled with AZ or ZA and down arrows) you're going to Data > Sort and selecting Header Row?

If you are and it still doesn't work, do you have more than one header row?

It sounds like what Gavin said - you aren't explicitly telling Excel if you have a header or not so Excel has to guess. An easy way for you to help Excel 'figure out' that you have headers is to put the header row(s) in bold while the rest of the table is not in bold.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

[Ctrl]+[A] is essentially identical to Select Current Region, except for the active cell, if that makes a difference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

I enter this debate with trpidation.....

but surely CTRL+A selects all cells (all 65k rows by 256 cols or 1m+ rows by 16k cols) and has no dependance on contiguous cells and this has been the case since at least Excel v7??

current region selects a rectangular range of cells contiguous to the active cell.

the quick shortcut for this is CTRL+*

in terms of active cell after the selection for CTRL+A the active cell doesn't change.
With CTRL+* the active cell changes to the top left cell of the selection for versions of Excel from v8(97). prior to that, and I'm not 100% sure when it changed, the active cell would be the bottom right cell of the current region selection.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

I than BOTH Loomah and John for illuminating me in this keystroke genre. I have been enriched! ==> [purple]*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

John,

Not sure how to do the quote thing, so I copied and pasted.

"I've never seen that behavior. Do you mean that, instead of pressing one of the 'quick sort' buttons (labeled with AZ or ZA and down arrows) you're going to Data > Sort and selecting Header Row?"

Anyhow, yes that is exactly what I have done. Typically just press the "quick sort" button. But when I have trouble... going into the data>sort and checking the box that says has header row. Yet, I still have the same problem.

Now, typically the sort feature works just as I expect, only on occasion does it bring the header into the sort, and it is specific to a new sheet/file that I may have received. If a sheet works originally, then it always works correctly.

I have checked the help on excel and know that by "bolding" the header row, that should tell Excel that it is a header....

Skip, I read your post as not to insert a row below the header. I will not make that a practice! but it worked on this particular sheet. Do you see a reason to remove the row? will it cause problems down the road?

Thanks to everyone who has posted on this issue!!''

Chris

 

[SkipVought]

FYI: "than" in the context of the previous post, must be read as "thank."

_{Than you very much!}

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

and trpidation in my post should have been trepidation
so, you'r wlcom!!

on the (unrelated) subject of shortcuts maybe time to revive this
thread68-463686
can't believe it's over 6 years old!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[AnotherHiggins]

Loomah:

From my observation, As of 2003, pressing [Ctrl]+[A] one time will select the current region if there is contiguous data around the active cell. Then pressing [Ctrl]+[A] a second time will select all cells as you suggest.

If the active cell is not in amongst populated cells then all cells will be selected the first time you press [Ctrl]+[A].

Is that not the behavior you have for [Ctrl]+[A] in Excel 2003/2007?

But thanks for the tip about [Ctrl]+[*]. I didn't know about that.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Loomah]

john
you could knock me down with a feather - i hadn't ever noticed that!!

i notice that the behaviour of the active cell hasn't changed though. that is using CTRL+A the active cell doesn't change while using current region the active cell becomes the top right cell.

as i first found out about current region on a vba course ( about a million years ago) i had a quick look at the vba generated by recording both shortcuts on the same table, purely out of interest

using CTRL+A

Range("B30:V50").Select
Range("I42").Activate

using CTRL+* on the same table with the same originally active cell

Selection.CurrentRegion.Select

as would be expected!

i know this isn't the vba forum so this is a mute subject but you'd have to agree programatically there is only 1 option!?! overall i'd (personally) stick with current region, in all instances, as it's decidedly more predictable!

anyway, nice interlude to what is just another really dull day at work!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[AnotherHiggins]

I noticed the same thing about the difference in which cell is active after using the two different methods.

Honestly this post is the first time I've heard about [Ctrl]+[*]. So thank you for that. In fact, have another star on me. And I actually just now discovered that it works with the asterisk on my number pad, not just [Shift]+[8].

And I agree with your point on VBA - I use [gray]Range("A1")[/gray].CurrentRegion.Select.

But I disagree with your penultimate sentence - both methods are equally predictable outside of VBA.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kjv1611]

Thanks for posting about the <Ctrl> + <*>

Works great! Apparently, it works the same whether using the Numpad *, or whether using:
<Ctrl> + <Shift> + <8>
from my tests.



--

"If to err is human, then I must be some kind of human!" -Me

 

[xlhelp]

One that Loomah sugested and a few more:

Select the current region around the active cell (the data area enclosed by blank rows and blank columns). In a PivotTable report, select the entire PivotTable report.
CTRL+SHIFT+* (asterisk)

Select the array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) containing the active cell.
CTRL+/

Select all cells that contain comments.
CTRL+SHIFT+O (the letter O)

In a selected row, select the cells that don't match the formula or static value in the active cell.
CTRL+\

In a selected column, select the cells that don't match the formula or static value in the active cell.
CTRL+SHIFT+|

Select all cells directly referenced by formulas in the selection.
CTRL+[ (opening bracket)

Select all cells directly or indirectly referenced by formulas in the selection.
CTRL+SHIFT+{ (opening brace)

Select cells that contain formulas that directly reference the active cell.
CTRL+] (closing bracket)

Select cells that contain formulas that directly or indirectly reference the active cell.
CTRL+SHIFT+} (closing brace)

Select the visible cells in the current selection.
ALT+; (semicolon)


These and more are listed here.


A man has only two choices: He can be right or he can be happy.

 

[Loomah]

and there's the thread i referenced earlier which i originally posted early 2003!

i tend to reject shortcuts i can't easily hit with one hand!! the main exceptions to this rule are ALT+; (as mentioned by xlhelp) and CTRL+K (add hyperlink) gets used a lot in my current 'job'!

john
in reference to predictability - just my opion/preference - nothing more!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[AnotherHiggins]

Well I refuse to accept that anyone can disagree with me. So there.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Loomah]

john, that's where we might differ - i can accept anyone disagreeing with me but refuse to accept they may be right!
%-)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[AnotherHiggins]

[thoroughly in the weeds]

john, that's where we might differ...

I'm afraid you haven't been listening. We don't differ. You actually agree with me.

Don't worry, it's a common mistake.
- - - -

...i can accept anyone disagreeing with me but refuse to accept they may be right!

Actually I like your line of thinking much better than what I said.

[/thoroughly in the weeds]


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.