Sorting macro

[Simon]

I am trying to write a macro that will sort a spreadsheet by certain
header definitions rather than by column.

Simply recording the action as a macro only registers the columns to be
sorted by, not the header definitions.

Does anyone know how this can be done?

Many thanks
Simon



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

The sort command only will accept column locations. You need to build code
that translates your header names into column locations and pass the column
locations to the sort command. An alternative would be to assign names to
the cells containing the headers which match the values of the headers.
Then you could use those as arguments to the key values.

My headers were

Header1 Header2 Header3 Header4
A B 100 230
C A 250 20

etc.

I named the cell (Insert=>Name=>Define) with Header1, Header1 and so forth.
This worked:

Range("B2").Sort _
Key1:="Header1", Order1:=xlAscending, _
Key2:="Header2", Order2:=xlDescending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom