Set the Header Row

[Dow]

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:
"Insert a blank row between the last header row and the other header
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.

 

[Gary Brown]

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

 

[Jim Cone]

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)




"Dow" <[email protected]>
wrote in message
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:
"Insert a blank row between the last header row and the other header
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]

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]

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



"Dow" <[email protected]>
wrote in message
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.