VBA Sort Custom Order list

Topic

New Reply

Hi there,

I am trying to do a custom sort on a table under headings A13:AL14 by a custom list which is situated in another sheet Data on Column F.

The data I want to be sorted by:

1. Column B which have data validation from sheet Data on Column F. (key 1)
2. Column C, Name, alphabetical (key 2)

The first row is 15 and the last row is 150. In the future is possible that the table to have more than 150 row and under the first row and last row are data that  I don’t want to be sorted.

After sorting data I would lie to insert criterial number in column A  from A 15 until last row A150

The code I  want to run on the active sheet because the name sheets is variable.

I hope I was explicit and I attached an example worksheets.

Thanks

Reply | Quote

Replies

Hi zeddy

I don’t now why but I can’t se you answer in the forum post neither the attached file!

Thanks

Reply | Quote

Hi

I don’t know what happened either! It was a long post! Here it is again:

In your attached file, on your [example] worksheet, your heading rows had merged-cells. Although it is possible to retain this kind of layout, I always find it much easier to do sorting when you ONLY HAVE A SINGLE HEADING ROW at the top of your data records. This single-headings-row allows us to use the Current Region feature to easily select a data-block for sorting (i.e. like pressing [Ctrl][A] on a cell to select the data block).

If you have complex heading requirements, you can always have a ‘hidden row’ just above your single-heading-row. This separates any extra ‘top row headings’ you might need etc etc etc.
So in my attached file, I have unmerged your headings and included a ‘separator-row’ (row 14) to allow us to use the Excel Current Region feature.

Excel by default has 4 Custom Sort lists:
(From the top-panel Ribbon use Home>Sort & Filter>Custom Sort>Order>Custom List…)
Sun, Mon, Tue, .., Sat
Sunday, Monday, .., Saturday
Jan, Feb, Mar, .., Dec
January, February, .., December.

It is possible to add a new Custom Sort List, either manually or via VBA.

Your Custom List on your second sheet shows the actual specific order you want to use for sorting. Note that this will put Order 10 after Order 9 as you require (Excel would by default put Order 10 before Order 2, because it sorts left-to-right, so you get Order 1, Order 10, Order 11, .., Order 19, Order 2, Order 20, Order 21, Order 3 etc etc.)

In my attached file, I have used the Excel Double-Click event to show you some Sort methods.

First of all, the double-click will ONLY do sorting if you double-click a cell in the headings-row (row 15).

So, when you double-click cell [B15] you get the sorting you asked for, using your Custom sequence.

I’m not sure what you wanted for column A, so I just filled it with a record reference number, 1 to 136 as per your original records. Sorting on this column allows us to return to the original order of your records.

If you double-click on heading cell [Info 1] i.e. cell [ai15], you will see you records sorted by value on that columns. This can be useful for seeing records sorted in value-order. Double-click that cell again and you get the reverse-sort.

You can double-click heading cell [a15] to return to the original order od records, after any sorting you choose.

To simplify things, I have added some named ranges, and I used Windows-freeze-panes to keep the headings visible when scrolling etc etc etc.

If you have any questions, please ask.

zeddy

 

Reply | Quote

I’ll try and attach as a zip file…

 

zeddy

 

custom_order-zeddy-1

Reply | Quote

Thank for your effort it will be useful for my future project.

Regarding for this worksheet I would like an independent  macro vba to do all this “custom sorting” and I would argue why

-the sheet is protected for editing so the double click will not be available.

-the dynamics of the person is frequently so are in and out often

-if a row is blank (between firs and last row), I would like that  the blank row to move to the end

-In column A after new sorting I want to insert current number from 1 until the last person (1,2,3, etc )

Thanks in advance

Reply | Quote

Hi

OK, so I used your original posted file and added a simple routine for the Custom Sort order you asked for. The sort ignores the heading rows.

I have assigned this routine to your button.

It was easier to use a named-range for your specified-sort-order-sequence, so I just named that as [block1]

Hope this gets you nearer to what you want.

Please ask if you need more assistance.

zeddy

custom_order-zeddy-2

Reply | Quote

Thanks you zeddy! To improve the vba I would like:

-the macro to run in “active sheet”! (sheet name in different in every month

-to add sequential numbers in ordered list  (consecutive numbers, 1, 2, 3 etc) from A 15 until last row where are data in column B (in range A15-C150)

Thanks!

Reply | Quote

Hi
I modified the macro to run in active sheet as requested.

The macro checks that there are at least 2 records on the sheet (minimum number required for a sort), using column B to find the last record row.

I then added some code lines to add sequential numbers in column A as requested.
The attached demo file shows 2 sheets, with 136 records and 95 records respectively, with the macro assigned to the same button on both sheets.

Hope you can adapt this for what you need.

zeddy

custom_order-zeddy-3

1 user thanked author for this post.

WSafm1985

Reply | Quote

Thanks you zeddy!  it`s working like a charm. you can close the topic!

Reply | Quote

It’s your thread, you can mark it as solved – top left, drop down.

cheers, Paul

1 user thanked author for this post.

zeddy

Reply | Quote

Hi Paul

I didn’t know about that dropdown.

So what about .xlsm files as attachments???

File types allowed for upload: .jpg, .jpeg, .jpe, .gif
.png, .bmp, .avi, .mp4, .m4v, .txt, .asc, .c, .cc,
.h, .srt, .csv, .mp3, .m4a, .m4b, .rtf, .pdf, .zip
.doc, .pot, .pps, .ppt, .xla, .xls, .xlt, .xlw, .docx
.docm, .dotx, .dotm, .xlsx, .xlsm, .pptx.

zeddy

Reply | Quote