Home |
Search |
Today's Posts |
#1
|
|||
|
|||
"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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
sounds like the "header row" option is selected.
|
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
Data Table - Does it work with DDE links and stock tickers? | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |