Excel -- VBA Custom Sort Order, Only Sort 1 Value to Bottom 1

[tqeonline]

Hi All,

I have a macro and I want to sort off of three columns:
[ul]
A
[/ul]
[ul]
R
[/ul]
[ul]
I
[/ul]

A - is just an alphabetical sort
R - I want to do a custom sort on
I - is just an Ascending value sort

This is how I want it to work - sort first off Column A, Then sort column R on a custom sort by "ignoring" Red, Yellow, and Green and only moving "Closed" to the bottom. Then "I" will just sort based on its value.

This is what I currently have:

'Sort by I (Budget vs. Cost Plan) - putting Closed at the Bottom
        Rows("1:" & LastRow).Select
        Range("A135").Activate
        ActiveWorkbook.Worksheets(X).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(X).Sort.SortFields.Add Key:=Range( _
            "A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets(X).Sort.SortFields.Add Key:=Range( _
            "R2:R" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, _
            CustomOrder:="Red, Yellow, Green, Closed", _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets(X).Sort.SortFields.Add Key:=Range( _
            "I2:I" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets(X).Sort
            .SetRange Range("A1:AH" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

The part that isn't working is Red, Yellow, Green, Closed - I want it to basically see Red Yellow Green as the same value and only move "Closed" to the bottom.

A data example would be:
A R I
1 Green -1
1 Yellow 2
2 Yellow 100
2 Closed 10
3 Red 50
3 Closed 250

It's basically a conditional grouping: If "Closed" then sort I ELSE sort I


- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[SkipVought]

hi,

You have not described the result that you get versus the result you expect???

Skip,

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

 

[tqeonline]

Dang it Skip - I tried to be detailed because I knew you had a history of being a BA.

The result i'm getting is that it sorts R Reds first, then Yellows, then Greens, then Closed.

What I want is it to sort R by leaving Reds/Yellows/Greens alone and putting Closed at the bottom then when "I" sorts for it to sort Reds/Yellows/Greens together and Closed as it's own group


- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[SkipVought]

how about a helper column that treates red, green, yellow as the same value? Do your second sort criteria on the helper column

Skip,

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

 

[tqeonline]

I had thought about that - just wanted to explore if there could be a solution without that first

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[SkipVought]

Not AFAIK.

Skip,

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