Macro sorting by 4 columns

[Guest]

I have a spreadsheet with a variable number of rows, which I need to sort by
4 columns (A, B, D, and C). Via menu, you can only sort by 3 columns
w/Excel, so I recorded the macro and tried to edit it to add the 4th
Key...but it doesn't work.

I've edited macros before without an error, so I'm somewhat at a loss to
figure out what the heck I'm doing wrong. Is Excel simply incapable of
sorting by 4 columns? Will I have to use QPro for this file?

Here is the 3 column sort code, as recorded:
Sub SortxManager()
'
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("B4") _
, Order2:=xlAscending, Key3:=Range("D4"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A4").Select
End Sub


Here is the 4 column version that doesn't work:
Sub SortxManager()
'
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("B4") _
, Order2:=xlAscending, Key3:=Range("D4"), Order3:=xlAscending, _
Key4:=Range("C4"), Order4:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal, DataOption4:=xlSortNormal
Range("A4").Select
End Sub


What am I doing wrong? Thanks in advance...

 

[Dav]

If normally you sort by column C. Then do a sort by A B D as your 3
columns you would get the result you require.

You could even record this as a keystroke macros

Regards

Dav

 

[Guest]

If normally you sort by column C. Then do a sort by A B D as your 3
columns you would get the result you require.

You could even record this as a keystroke macros

I cannot do this, this is what the help function in excel tells you to do
for a four column sort. Unfortunatley, I need to sort all 4 columns on the
fly.

Sorting by column A separates a list 175 projects into 6 different sections
by project manager. Sorting by column B then organizes each managers
projects by town, sorting by D then organizes each town's project by project
number. In some instances, with larger projects, project numbers in column D
are further organized into different project phases, so I need to take the
sort to a fourth column to sort by project name within the project numbers.
The rest of the data in each row of the spreadsheet are a twelve week
man-hours projection for our design staff, each of which is tied to a
specific project name.

If I were to sort by manager (A), I would then need to sort each managers
projects by the remaining three columns. Hence, I would need 7 macros (and
as we get more managers, more macros). In each managers case, the number of
sort rows change dependant on insertion of new projects/designers and
deletion of closed projects. I have no way to accuratley set the selection
range for each manager, so I would have to run the sort smanually, every week
when the manhours projection updates.

I run Quattro Pro on my home PC and can run a five column sort direct from
the sort menu, and I've gone up to 8 within a macro. Qpro did that 11 years
ago (my first employer didn't use Excel). I have to beleive that Excel 2003
is as capable as QPro 1995...certainly not thru the menu, which obviously
only allows the three column sort, but at least within a macro.

Thanks for taking a stab at it though!