Can I sort a table by applying columns in a different order to the sort request?

TheRedCardinal

Active Member
Joined
Jul 11, 2019
Messages
250
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I have a table on a worksheet with upto 15 column headers.

Elsewhere in my workbook, I have a "Settings" sheet which contains these 15 headers and various settings associated with those columns, such as format, width, bold or not etc.
One of them is "Sort" and the column for that consists of either a blank (don't sort) or a number (the order of the sort)

My macro loads these into an array in order of sorting preference, so that Array(1) contains the column number of the first priority, Array(2) the second etc.

But when I try to apply those codes I get a Type Mismatch error. I think it's because I'm referencing the column wrong.

Here is my code:

VBA Code:
With WS2.ListObjects(1).Sort

    .SortFields.Clear
    
    If SortColNumbers(1) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(1)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(2) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(2)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(3) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(3)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(4) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(4)), SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(5) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(5)), SortOn:=xlSortOnValues, Order:=xlAscending
    
    .Header = xlYes
    .SortMethod = xlPinYin
    .Apply
    
End With

During testing, my array had number 3, 8 and 6, and the other two are empty.

It fails on the first line, but after "passing" the IF test.

What have I done wrong?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Can you check the type of your SortColNumbers(1) variable?
 
Upvote 0
In this case I think it would be better to initialize your array it as an array of integers or maybe long numbers. I'm not 100% on this, but I think .ListColumns() is expecting an integer or maybe a long number and it's getting something else that it can't handle.
 
Upvote 0
Hi,
Sorry for the delay - I took a break from Excel for the weekend!

I have set the array up as both Long and Integer and I get the same error in the same place.

Thanks for the idea...
 
Upvote 0
I have sorted it.

I needed to specifically refer to the range, not just the column:

VBA Code:
With WS2.ListObjects(1).Sort

    .SortFields.Clear
    
    If SortColNumbers(1) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(1)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(2) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(2)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(3) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(3)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(4) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(4)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    If SortColNumbers(5) <> "" Then .SortFields.Add Key:=.Parent.ListColumns(SortColNumbers(5)).Range, SortOn:=xlSortOnValues, Order:=xlAscending
    
    .Header = xlYes
    .SortMethod = xlPinYin
    .Apply
    
End With

Works now!
 
Upvote 0
Solution

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top