Make "Header row" default search mode?

  • Thread starter Clueless in Seattle
  • Start date
C

Clueless in Seattle

I have a very simple worksheet I use as a task manager. One of the
columns contains values from 1 - 5 representing the urgency of the
tasks. I'm constantly adding and deleting tasks, and changing the
urgency values for tasks as my situation changes. Which means that
I'm constantly re-sorting the data so that the most urgent tasks
appear at the top of the sheet.

Somehow I've managed to set the spreadsheet so the Sort command thinks
that I have no header row. So each time I sort I have to manually
check the "Header row" button under "My list has."

Isn't there a way to tell Excel once-and-for-all that my list will
always have a header row so that will be the default sort mode?

Also, I always repeat the same sort, i.e., to sort on the urgency
values column. But every time I sort, I have to manually tell Excel
which column to sort on. How can I tell Excel to repeat the previous
sort or something like that, so it automatically sorts on the same
column each time?
 
B

Biff

Hi!

Since this is something you do frequently, the next time
you have to do this just turn on the macro recorder and
create a macro to do the sort operation.

Biff
 
G

Gord Dibben

Use a different format for the row of headings, and Excel should exclude
them from the sort. For example, make the text bold, or a different colour.

Record a macro using the Macro Recorder while setting up your sort.

Assign it to a button or shortcut key combo.

Gord Dibben Excel MVP
 
G

Guest

In the recording session, this line will be generated and
should take care of it:

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
 
C

Clueless in Seattle

I already had the header row formatted with bold font and a dark
bottom border. But I tried changing the header row color too, per you
suggestion, just to see if that might do the trick. But even with the
color changed, the sort still defaulted to "No header row."

So I tried an experiment. I selected all the data in all the rows
below the header row and copied it. Then I opened a new blank Excel
worksheet and pasted the data in at A2 so the top row would be blank.
Then when I clicked Sort in the new worksheet, and voila! It defaulted
to the "Header row" button being checked.

So I copied the data from the new sheet and pasted it back into the
old sheet, leaving the original header row. And guess what!

Right! The Sort function defaulted to "Header row."

Of course I haven't a clue as to why that cutting and pasting between
sheets corrected the problem, but it did.

So now, on to trying to figure out how to create a macro in Excel...
 
G

Gord Dibben

Thanks for the feedback.

Also Clueless, Gord

I already had the header row formatted with bold font and a dark
bottom border. But I tried changing the header row color too, per you
suggestion, just to see if that might do the trick. But even with the
color changed, the sort still defaulted to "No header row."

So I tried an experiment. I selected all the data in all the rows
below the header row and copied it. Then I opened a new blank Excel
worksheet and pasted the data in at A2 so the top row would be blank.
Then when I clicked Sort in the new worksheet, and voila! It defaulted
to the "Header row" button being checked.

So I copied the data from the new sheet and pasted it back into the
old sheet, leaving the original header row. And guess what!

Right! The Sort function defaulted to "Header row."

Of course I haven't a clue as to why that cutting and pasting between
sheets corrected the problem, but it did.

So now, on to trying to figure out how to create a macro in Excel...
Click to expand...
 
Top