Excel - Set the Header Row

Asked By Dow on 28-Feb-09 02:26 AM
I looked through the forum but did not find an answer.

The header information in my spreadsheet is in row 2.

Excel has 3 options for header row: Yes, No, and Guess.  None of these
work on my worksheet to use the second row to sort by.

Selection.Sort Key1:=Range("C2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

I have a work around that I adapted from Tom Ogilvy:
rows.
Sort normally
Remove the blank row."

I added the code for that and it works fine.  It feels like there
should be a way to say "Use Row 2 as Header Row".

Thanks,

Dow.




Jim Cone replied on 27-Feb-09 12:18 AM
Sorting is done by the specified column.
In your case column C.
Using xlYes for the header option means that the first row
of the data will be Excluded from the sort.
Using the xlNo option Includes the first row in the sort.

I never rely on the xlGuess option.
--
Jim Cone
Portland, Oregon  USA
(Special Sort Excel add-in; two dozen additional ways to sort)




I looked through the forum but did not find an answer.

The header information in my spreadsheet is in row 2.
Excel has 3 options for header row: Yes, No, and Guess.  None of these
work on my worksheet to use the second row to sort by.

Selection.Sort Key1:=Range("C2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I have a work around that I adapted from Tom Ogilvy:
rows.
Sort normally
Remove the blank row."
I added the code for that and it works fine.  It feels like there
should be a way to say "Use Row 2 as Header Row".
Thanks,
Dow.
Dow replied on 28-Feb-09 02:27 AM
I understand that, and I will definitely keep it in mind for my
projects.  The issue I am having though is that I want to be able to
make the second row my header and exclude it from my sort.  xlguess,
xlno, and xlyes do not allow me to do that.

Any thoughts on how to make that happen?

Thanks again,

Dow.
Jim Cone replied on 27-Feb-09 09:21 AM
So, if you have Rows 1 thru Rows 10 to sort...
Are you saying that you want to sort Rows 1 and Rows 3 to 10
and that row 2 should remain in place as the second row?

Sorting doesn't work that way.  It sorts all specified rows -
except for the first row - if you tell Excel that it is a header row.
--
Jim Cone
Portland, Oregon  USA



I understand that, and I will definitely keep it in mind for my
projects.  The issue I am having though is that I want to be able to
make the second row my header and exclude it from my sort.  xlguess,
xlno, and xlyes do not allow me to do that.

Any thoughts on how to make that happen?
Thanks again,
Dow.



On Feb 26, 10:18 pm, "Jim Cone"
GaryBrow replied on 26-Feb-09 02:36 PM
Define your range first...
Range("C2:F10").Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown