Multi-level sorting button

Hi all

Please note first of all, my VBA knowledge is zero, but I am pretty good with excel and I did programming a long time ago at college so I can understand stuff. I'm just not experienced.

I have an Excel Table with a lot of columns (25 of them actually) and with a lot of items (about 2000 rows of data).

The correct way to sort it is multi-level and requires 9 different sorts. This takes a while (a bunch of clicking), is prone to mistakes, and more importantly other users may not know the right way to do this. So I want a magic button that does it all.

Before we get to the button itself (that's probably the easy part, although I've never done it), I started looking at the code that would do the sorting. I expected it to be pretty simple. With the help of Google and some YouTube videos, I ended up with this:

Please Login or Register  to view this content.

Please note:
- the Workbook is called "Invoices & Cashflow.xlsm".
- the Sheet on which the table is is called "Invoices" ("Sheet3" when looking at VBA dashboard)
- the Table itself is also called "Invoices" (this is confirmed in the Name Manager)

The error I am having is that it doesn't work. No matter how many new levels I add for sorting, only the first one works, i.e. the table gets sorted by "Cost Type" in ascending order, and everything stops there. This is of course not ideal. The last step should be "Payment Date", which supersedes all others.

Why aren't the other levels working? "Cost Type" is only the first step out of 9 in total...

Also, side note: I have no idea why ".Header = xlYes" is necessary, or what it does. I've noticed some online examples use it. It seems to make no difference to the result...

Thanks for helping out, much appreciated.

PS: any tips on the button are also appreciated, but I was going to search Google first before asking

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I actually had such a code with just 2 levels of sorting but added 7 more, tested and the following is working for me.
Of course, replace Table1 and it's columns [1], [2] ... etc. with your own.

Please Login or Register  to view this content.

All we need are the headers in the right columns and rows.

Also the type of data.

So three rows in total

Ok please see attached - does this work?

I'm not using the actual workbook. I copied all the contents of that table and pasted VALUES ONLY into a new, blank workbook.

This new workbook has a simple data set with simple filters... It is NOT a defined "table" (unlike the actual workbook I use).

I've changed the name of 3 entities to random stuff, and left all the data pertaining to them ONLY. I've deleted all the other data. So there's only 3 "Counterparties".

In this new workbook, with super simple formatting, no data validation, no conditions, no special anything, just plain data across 25 columns ... The sorting I want still doesn't work...

See attached. Also an image of what I am doing. If you do it all manually, you get the result I want. If you do it via Excel's multi-level sorting, it doesn't work properly. The VBA solutions above also don't work.

Thanks for looking into this!

A friend suggested recording a macro of the sorting and testing it; if it works, then reviewing the code.

Good news is, it worked.
Bad news is, it's slow as hell. About 4x slower than manually clicking and sorting 9 times... The whole thing takes about 30 seconds... And when I tried it using the keybaord shortcut I designated for it, Excel froze. Lol. Tried this twice, froze on both occasions...

Maybe the code is too overcomplicated and can be drastically simplified. But I'm no coder and this is only my second experience with VBA (first one was 7 years ago).

If anyone could be so kind to have a look and clean it up, would be much appreciated.

Please Login or Register  to view this content.

Edit: at first glance, it appears that the below code can be removed 8 times, and only left once (the "Clear" command first, before any sorting, and the rest at the bottom, right before the end).

Please Login or Register  to view this content.

This made the code a lot simpler and smaller, but it did not work. Sorting did not work as expected (original error) ...

Hi MagellanOfBass,

this is what i mean

Please Login or Register  to view this content.

Grtz

Post Deleted.

Hi Joske920

That makes no sense... But guess what, I tried it, and it looks like it works!! And it's also quite fast...

Can anybody explain why it works?

Edit: it also works when using Excel's integrated multi-level sorting in inverse order. Why or how, I don't know. But the main takeaway is no VBA needed.