Excel 2003 Sort Macro 1

[bdjb]

Hello,
I need to create a sort macro based upon Column Header name instead of "B2" reference as columns may be in different orders. I recorded a macro, but need to replace the Key with column headers, is that possible?

Selection.Sort Key1:="Office", Order1:=xlAscending, Key2:="Client ID" _
, Order2:=xlAscending, Key3:="Referral Rcvd", Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

Thanks in advance!

 

[SkipVought]

Hi,

First you state, "replace the Key with column headers."

Then, in your code you have Header:=xlNo. I think you want xlYes.

Also, you what to EXPLICITLY define where you table is. Hence the YourSheetObject and the correct intersection oc Cells(row, column) for the top left cell in the table. The solution assumes row 1 column 1...

    With YourSheetObject
        .Cells(1, 1).Sort _
            Key1:=.Rows(1).Find("Office"), Order1:=xlAscending, _
            Key2:=.Rows(1).Find("Client ID"), Order2:=xlAscending, _
            Key3:=.Rows(1).Find("Referral Rcvd"), Order3:=xlAscending, _
                Header:=xlNo, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal, _
                DataOption2:=xlSortNormal, _
                DataOption3:=xlSortNormal
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bdjb]

Thanks Skip,
That got me in the right direction, just one tweak needed please. I have 3 header rows (2 are titles, one is the actual column headers), how can I modify to ignore those 3 when sorting?

I tried:

With ActiveSheet
.Cells(4, 1).Sort _
Key1:=.Rows(3).Find("Office"), Order1:=xlAscending, _
Key2:=.Rows(3).Find("Client ID"), Order2:=xlAscending, _
Key3:=.Rows(3).Find("Referral Rcvd"), Order3:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

 

[SkipVought]

A properly construsted table has ONE row of headings and is contiguous.

If you have OTHER data, isolate it from your table by inserting an empty row ABOVE the heading row.

So in your case you would have rows 1 & 2 with external headings, row 3 empty and row 4 with table headings: rows 5 and following with table data.

modify the procsdure to FIND in row 4 and locate the upper-left cell in row 4 column whatever.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bdjb]

Thanks again,
I'm stuck with layout as that is what Management wants. I'll follow your suggestion as to empty row.

 

[SkipVought]

"I'm stuck with layout as that is what Management wants."

I'm not sure I understand. You can make it APPEAR that you have 3 heading rows, by HIDING the empty row between the table heading and the extraneous data.

However to avoid problems that can be a chronic plague, your data structure MUST be sound.

Multiple heading rows IN A TABLE will NOT work properly!!!

If your management cannot live with that, then they are ignorant!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}