Start Free Trial

asked on

Custom Sorting Using Macros

I have 2 macros in this file. 1) to sort by comment colors, then ship date and 2) sort the certs bucket by ship date only. When I run the macro it runs it for the buckets with the exact cells I highlighted when I created the macros. Currently every time I want to sort the other buckets by option 1, I have 5 steps which I thought creating the macro would eliminate that so I didn't do something correctly but not sure what. I want to be able to use a macro to sort any of the buckets by option 1 no mater how many rows are in the bucket. If you would also explain to me what I did not do correctly that would also be helpful. Thx You
FINAL-CERT-SUMMARY---MACROS.xlsm

I'll take a look at this later.

It looks to me lik Excel is not recognizing your separate blocks of data. Add a completely empty row between each block.

The Ranges will need amending accordingly

It might be better to format each block as Table, I'll check which works best

Marcia

The AutoFilter on the sheet includes all the data. I'll take a good look and get back to you.

Hi Marcia

I've amended the sheet and also one set of code ( Comments_Sort). Can you yest this Ctrl+Shift+S and let me know if it what you expect.

I'll keep working on it because of the time difference.
FINAL-CERT-SUMMARY---MACROS--1-.xlsm

I've just noticed that the code is filtering the wrong column.

I've amended the code and changed the data to a Table. This will allow you to add data and the macro will automatically pick th it up.

Note it currently only works on the one block of data.
FINAL-CERT-SUMMARY---MACROS--1-.xlsm

I've added code to sort the Certs block. This code works with the Range, not a Table.

I've improved the code for the ListObject (OP).

The most important thing to remember when working with data is that the actual block of data should have no completely empty rows or columns. In your original workbook there was no empty rows dividing the data so to Excel it was one huge block of data which caused the problems. I've isolated each block of data with empty rows and columns, including your total cells.

Try this and let me know if it was what you wanted.
FINAL-CERT-SUMMARY---MACROS--1-.xlsm

Try this file...
what I did to solve the sort range which was not dynamic
Add a line after OP Product, add named range in cell b58 to compute OP Product Header row (ProductCommentStartRow) with folmula = Row() +1
add a line at the end of OP Product line, add named range ProductCommentsEndRow with formula in B75 =MATCH(A75,A:A,0) -1 that will give the last row to sort on.
Modified th associated macros
You may apply this simple technique to every block of data
If you distribute this file, hide the helper rows :)
Stephane
sf-FINAL-CERT-SUMMARY---MACROS.xlsm

ASKER

I'm wondering if a macro is the correct way to go with this because I feel like there is room for someone else working on this file to mess it up since multiple people work from it. I just want to make sure I understand the file now.
1. The lines that were inserted at the beginning and end of each section is what is assisting with the custom sort?
2. Is it easy to add additional sort options by myself?
3. Is there a way to create a 'custom sort' using the sort (see attachments) and add it to the quick access tool bar?
Starndard-Sort.png
OPS-Sort.png

It is important that your blocks of data are separated, hence the need for the empty rows and columns, otherwise Excel will attempt to sort the whole data.

I'll look at making a dynamic macro, but it would probably require a cell in the block to be sorted being selected first manually. Unless you want to sort each block of code at once

ASKER

Hi Roy, I am OK with selecting the cells first. That is actually my preference given that we add rows a lot.

Ok, I'll try to make the code more dynamic for you, do you want to use two different sorts on each block of data?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

My apologies for the delayed response. I've been out of the country since 8/28. I will test and provide update tomorrow.

No problem

ASKER

Roy, when I opened the file I got an error message (error_01). Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Removed Records: Sorting from /xl/worksheets/sheet1.xml part

After saying yes, I tried to sort using the assigned shortcut and go the next error (error_02).
Error-Message-Spreadsheet_01.PNG
Error-Message-Spreadsheet_02.PNG

Ill check it

Hi Marcia, I'm really busy at the moment, but I'll have some time to look at this at the weekend

ASKER

Roy, thank you very much!

Is this working now.