Macro to Sort Multiple Dynamic Ranges by Date

[crikreef]

I have a sheet with several different sections of rows of data on a sheet. Each section contains a variable amount of rows below it but the same amount of columns.

Column A contains the heading of the section and then in the rows of data below, the date for each row. I need a macro that will sort the rows in each section by this date while keeping the rows in their respective sections, under that section's title. I would want it sorted by oldest at bottom, newest at top. I believe if I set each section as a dynamic range and then run a macro that will sort each named dynamic range I can get the desired outcome but that's as far as I can seem to get.

The book added has an example of the before (sheet 1) and after (sheet 2) that I am looking for and I have named each range as their respective Section.

[xladept]

Hi crikreef,

Try this

Please Login or Register  to view this content.

[crikreef]

That works beautifully. The only problem is that I gave a generic example. I need it to work for sections that have specific names, not the generic Section 1, Section 2 etc...

[xladept]

Hi crikreef,

Perhaps you could just use the one space separator and assume that the first range started in row 2!

Please Login or Register  to view this content.

[MarvinP]

Hi crikreef,

This looks like a Pivot Table answer to me. If you move the section number to a column by itself and then play with the Pivot Table options and design you get what I show in the attached. A single table is much easier to work with than multiple. See if this attached helps.

[crikreef]

Ya I would love to do that, as I'm very comfortable with pivot tables, but the person I'm making it for refuses to use them so I need go this route.

I did figure out a work around though. I just inserted a new column A, and set the cells next to the dates to pull the date over and then added the generic section headings to each section and then hid the column. Seems to be working!

Thanks for your help.

Still interested in seeing if it is possible to do the macro for the dynamic ranges though if anyone knows how to do that (or if it is possible)

[jindon]

This should work.

Please Login or Register  to view this content.

[crikreef]

Works on the generic book I uploaded but when I try it on my real book it says:

Run-time error '1004:
This operation requires the merged cells to be identically sized, even though there are no merged cells on that sheet.

And the debugger is highlighting

.Sort key1:=.Cells(1), order1:=1, Header:=xlYes


Attached is another workbook that is more representative of what I need the macro to work on.

[jindon]

Merged cell?
I don't see any of them

Please Login or Register  to view this content.

[crikreef]

I found it. It wasn't in the book I uploaded. It was in one the cells that I had deleted before uploading the cleaner version. Looks like it works!

Thanks!