Custom sort along with standard sort I am trying to get this sort to work with a

Question

Custom sort along with standard sort

I am trying to get this sort to work with a Custom sort

With Worksheets("tmp").Range("A1")
' custom sort order
Dim sCustomList(1 To 10) As String
sCustomList(1) = "1"
sCustomList(2) = "2"
sCustomList(3) = "3"
sCustomList(4) = "4"
sCustomList(5) = "5"
sCustomList(6) = "6"
sCustomList(7) = "7"
sCustomList(8) = "8"
sCustomList(9) = "9"
sCustomList(10) = "10"
Application.AddCustomList ListArray:=sCustomList

i = .Range("A1").CurrentRegion.Rows.Count
       With Sheets("tmp")
        With .Sort
         With .SortFields
             .Clear
            .Add Key:=Range("Y2:Y" & i), SortOn:=xlSortOnValues, OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("B2:B" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("M2:M" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("K2:K" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("R2:R" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

What I am trying to do here is make sure the sorts when being placed in the new worksheet actually break out by the following groups

Range Y = type of service (1 - 10)

Range B = Site number location (12344555)

Range M = type of prod model (AAA, AAO bbc, bab, 34X, z12 etc.)

Range K = start date ( standard date format)

Range R = Unique serial number

The problem is that when it comes to Y and M they seem to group together meaning if I had 2 sub prod models of products with the same serial but 2 model it would group them together, I need them to group them separately but still make sure they following the other order exactly of Serivce, Location, Model, Start date, Unique Serial.

If I use the standard

             .Add Key:=Range("Y2:Y" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

            .Add Key:=Range("B2:B" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("M2:M" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("K2:K" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Range("R2:R" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

for all instances I cannot get the sort to work properly because the range Y sort is then overridden by the range M sort

basically I cannot get the Y and M range sorts to work together nicely

Any help would be greatly appreciated

Thanks !!

Explanation / Answer

Hi..

if values in column Y are from 1-10 you don't need custom list

unless you want to sort like this...

v= Array("1", "3", "5", "7", "9", "10", "2", "4", "6", "8")

also,

it's not necessary to save the custom list

Application.AddCustomList ListArray:=sCustomList

now,

for column M, create a custom list

v=Array( "AAA", "AAO", "bbc", "bab", "34X", "z12")

and in vba..

.Add Key:=Range("M2:M" & i), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=Join(v, ","), DataOption:=xlSortNormal

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.