Macro to Merge Sequential Dates in Rows

[MSmithson]

Hello all,

Trying to write a macro that will merge sequential dates within two or more rows.

I have the columns Title, Start Date, End Date, Format, Rights1, Rights2, Rights3 and Notes. These columns can be in any column letter, which is why I’d like to use the header in row 1 to identify these columns.

If the Title matches the column below, and Rights1, Rights2, and Rights3 all match and the dates are sequential, then merge these two rows into one row with the earliest start date and the last end date in the start and end date columns. The Format and Notes columns are merged together so no data in these columns is lost in the merge.

See examples 1 and 2 in attached sheet "Sequential Dates Merge".

Notice that on the first example, the Start Date of 3/22/11 is used and the End Date is 3/30/11 is used in the merged row. This is because the End Dates of 3/25/11 and 3/26/11 are sequential. The Formats are merged together and the Notes columns are merged together keeping any formatting such as font and color.

Since I’m working with up to several thousand rows, the macro may have to start at the last row and work it’s way up, using a -1 instead of a +1 for the sequential dates.

Something like: IF (Title = Title) and (start date = start date -1) and (end date = end date -1) and (Rights1 = Rights1) and (Rights2 = Rights2) and (Rights3 = Rights3) THEN

Merge (Union) of Rows and Merge Format and Notes columns together to include any data in rows into new row.

Any help at all is most appreciated – thank you so much!

Question also posed at mrexcel: http://www.mrexcel.com/forum/showthread.php?t=537943