How to sort horizontally selected columns in Excel by values in 8

[naumlitvin]

I need to create a macro to sort horizontally a worksheet with about 60
columns and 1200 rows. The sort should work on selection of columns, which is
from column I to BH (9 - 60). Sort should work on values in 8 non-adjacent
rows. My first thought was to transpose selection in another worksheet, sort
using concatenated values (and perform some averaging) and then transpose
back. But max # of cells in a row seems to be limited by 256 in Excel 2003
(and 2007 is not an option for several reasons) so transpose does not seem as
a feasible plan...

I have a sample excel file with "before" and "after" worksheets illustrating
what I need but I do not see how to upload a file here...

Any ideas would be appreciated.

 

[Ronald R. Dodge, Jr.]

One way to go along your thought process, rather than attempting to put all
1200 rows into columns via the transpose, do only on the ones that you are
actually planning on sorting as well as a column for keeping track of what
column numbers the data is corrently on.

Once the key columns has been sorted with the column that contain the column
number of the column each of those records are in, you can then have it go
down the list and pull in the column number to copy that particular column
data into a new worksheet column by column.

About the only other way I can think of to get around it is to build a
couple of class objects, one for each individual with the various
properties, and the other as a collection of all of those individual class
objects. Similar to how the Worksheets collection object contains all of
the individual worksheet objects. However, that's more involved, so not
sure if you want to get into that sort of effort or not.

Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000