Macro to sort variable rows of data by a column in multiple sections

[mannuhs]

I need a macro that will sort the data between columns A - O by column J in ascending order. To make it a little more complicated, the first and last rows are not defined. It depends on how much data is exported from a Crystal Report. It could be 1 row of data to 30 rows of data. And it will be 3 separate sections of rows. So for the last 2 sections the first row varies by how many rows are in the first section. I attached the spreadsheet so you can see what I am talking about.
When I export this report to Excel, rows 1 - 3 will always be the same. The data in the rows under P1 Level Calls sub heading will vary on how many calls there were that day, but the first row for that section will always be row 4. Then the "Total # P1 Calls" row will always be at the end of that section. Then there are 2 empty rows, then The NCR CE Meet section and a varying about of rows for that data underneath, then the 3rd section "NCR On Site" under that with varying rows of data. I need the macro to sort the sections of rows by column J in ascending order, then highlight the rows where it says "Missed" in column J. Is this possible?

[jaslake]

Hi mannuhs

You posted an .xls file but your profile shows Excel 2007. Does the required Code need to work in only Excel 2007 and later or does it also need to work in earlier versions?

[mannuhs]

I use Excel 2007, but this particular file I save in xls. format because I distribute it to others. If the code just works for 2007 that's fine. I can just save the file in an earlier format before I distribute it.

[jaslake]

Hi mannuhs

Sort syntax is different for Excel 2007. This syntax will work in either. You indicated to sort through Column O but your Data extends to Column R so that's what I used. If you truly want Column O change the rng references.
Let me know of issues.

Please Login or Register  to view this content.

[mannuhs]

Wow that worked perfect! Thanks so much!

[jaslake]

You're welcome...glad I could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.