Sorting mixed content.

I have a worksheet with a row of mixed cells near the top.
By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).

GOAL: I want to sort the columns to the following order:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.

So far all I have been able to accomplish is:

First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.

OR

First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.

This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.

I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.

Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.

I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.

Thanks in advance for any help you can give,
metsci

Hi metsci,

1. To solve your problem manually:
a. Sort the entire range by row 4 ASCENDING. This will put the text in order in the proper columns.
b. Then sort 'C4:H7' by row 4 DESCENDING. This will put the numbers in the proper columns.

2. The following Macro will do the same thing as the manual sort:

Sub SortDataByRowsUsingTheSameRangeAllTheTime()
  'This sorts the range using row 4 as a key:
  'a. Numbers is descending order
  'b. Followed by text in ascending order
  '
  'as generated by the Macro Recorder and modified for 'Pretty Print'

  Range("C4:M7").Select
  Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, _
    Header:=xlNo, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlLeftToRight, _
    DataOption1:=xlSortNormal

  Range("C4:H7").Select
  Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, _
    Header:=xlNo, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlLeftToRight, _
    DataOption1:=xlSortNormal

End Sub

3. See the attached file for a Macro that is probably a good starting point for your final needs.
The macro uses variables to define the 'range' of the sort area and the 'sort key'.

Please feel free to ask if you need help with any of the code or you have any more questions.

Lewis