"Sort Data" omits Row 1
 

I have many times experienced that when I select a range of data (e.g. 100
rows), and ask Excel to sort it in ascending/descending order, it only sorts
rows 2-100. The values in row 1 are left in their original position,
regardless of where they rank according to my criteria.
I am now buliding a database in which I want to include several sort-macros,
where this problem appears. Any suggestions for me?

Thanks

Hi
goto 'Data - sort' and uncheck that row 1 is a heading row

--
Regards
Frank Kabel
Frankfurt, Germany

Zyvind wrote:
I have many times experienced that when I select a range of data
(e.g. 100 rows), and ask Excel to sort it in ascending/descending
order, it only sorts rows 2-100. The values in row 1 are left in
their original position, regardless of where they rank according to
my criteria.
I am now buliding a database in which I want to include several
sort-macros, where this problem appears. Any suggestions for me?

Thanks

sounds like the "header row" option is selected.

Thank you for a quick and helpful response. Changed macro setting from
xlGuess to xlNo, and that did it!

"dlw" wrote:

sounds like the "header row" option is selected.

Zyvind,

Ah-HAH! You didn't tell us it was a macro, and what the code is. Just for
a little clarification, it wants to know if the first row is a column
header, and should remain at the first row, or a regular record, where it
should be sorted. When you use Data - Sort, you specify whether the first
row is a header or not. When you use the sort buttons on the toolbar, it
guesses.

Here's the rub: Unfortunately, even when you use Data - Sort, where you
specify the first row as being a header or not, the macro recorder might put
xlNo, xlGuess, or whatever it wants, depending on whether the first row
looks to it like a header (it looks for different formatting and I don't
know what-all). I've found it's best to check what it's recorded.

You'd expect to get xlGuess when using the sort buttons on the toolbar, as
you have no way of telling it about the first row being a header or not, and
it must guess. But when you've used Data - Sort, I don't know why it
doesn't faithfully record your choice about the first row.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Zyvind" wrote in message
...
Thank you for a quick and helpful response. Changed macro setting from
xlGuess to xlNo, and that did it!

"dlw" wrote:

sounds like the "header row" option is selected.

I _WANT_ "Sort Data" to omit Row 1 when I select a cell in a column and use
the Sort Ascending button. This works in data tables with not many rows but
won't with the 583 rows in the one I'm working on. How do I force it to do
this without a hidden Row 2, or using "DataSortHeader Row" each time (which
doesn't "stick")?

"Frank Kabel" wrote:

Hi
goto 'Data - sort' and uncheck that row 1 is a heading row

--
Regards
Frank Kabel
Frankfurt, Germany

Zyvind wrote:
I have many times experienced that when I select a range of data
(e.g. 100 rows), and ask Excel to sort it in ascending/descending
order, it only sorts rows 2-100. The values in row 1 are left in
their original position, regardless of where they rank according to
my criteria.
I am now buliding a database in which I want to include several
sort-macros, where this problem appears. Any suggestions for me?

Thanks

Try giving the header row a different format--bold, underlined.

Sometimes that's enough to give excel the hint that that row is not part of the
data.

(Although, I find the Sort buttons on the toolbar too dangerous--it's too easy
to not include adjacent columns by mistake.)

Philip Hinton wrote:

I _WANT_ "Sort Data" to omit Row 1 when I select a cell in a column and use
the Sort Ascending button. This works in data tables with not many rows but
won't with the 583 rows in the one I'm working on. How do I force it to do
this without a hidden Row 2, or using "DataSortHeader Row" each time (which
doesn't "stick")?

"Frank Kabel" wrote:

Hi
goto 'Data - sort' and uncheck that row 1 is a heading row

--
Regards
Frank Kabel
Frankfurt, Germany

Zyvind wrote:
I have many times experienced that when I select a range of data
(e.g. 100 rows), and ask Excel to sort it in ascending/descending
order, it only sorts rows 2-100. The values in row 1 are left in
their original position, regardless of where they rank according to
my criteria.
I am now buliding a database in which I want to include several
sort-macros, where this problem appears. Any suggestions for me?

Thanks




--

Dave Peterson

Thanks Dave: I tried that but it still didn't work! Eventually found out why
though: my predecessor had data adjacent to the data-table in column BI, row
200-odd, which didn't have anything in BI1 (as a header). Grrrr. Turns out
that Excel then treats the whole thing as being without a header row (wish it
would say so in Help!). Thanks for your input.
PS To do the above, I found a blank cell in the table, then used
=counta(BI:IV) to check for extra data, then wrote =counta(BI2:BI1000) into
BI1, and pasted it waaay to the right. This showed which columns had unwanted
data.
Philip

"Dave Peterson" wrote:

Try giving the header row a different format--bold, underlined.
Sometimes that's enough to give excel the hint that that row is not part of the
data.
(Although, I find the Sort buttons on the toolbar too dangerous--it's too easy
to not include adjacent columns by mistake.)
Philip Hinton wrote:
I _WANT_ "Sort Data" to omit Row 1 when I select a cell in a column and use
the Sort Ascending button. This works in data tables with not many rows but
won't with the 583 rows in the one I'm working on. How do I force it to do
this without a hidden Row 2, or using "DataSortHeader Row" each time (which
doesn't "stick")?
<snip