Select range between blank cells

Hiya

This is my first post, so I hope I don't do anything wrong. I have a column with dates, seperated randomly by empty cells. Like this:


2004-03-03 00:00:00
2004-03-29 00:00:00
2004-07-27 00:00:00

2004-03-03 00:00:00

2004-03-03 00:00:00

2004-11-30 00:00:00
2004-12-06 00:00:00

2003-09-29 00:00:00
2004-07-19 00:00:00
2005-03-16 00:00:00
2005-09-27 00:00:00
2008-06-16 00:00:00
2010-06-11 00:00:00

2003-10-28 00:00:00
2003-12-03 00:00:00
2003-12-22 00:00:00
2004-03-03 00:00:00
2008-06-16 00:00:00

2003-12-03 00:00:00
2006-04-07 00:00:00
2006-11-02 00:00:00
2008-06-16 00:00:00

I need to know the earliest date within each of the "mini-ranges" seperated by the blank cells. So the first would be 2003-03-03, the second would be 2004-03-03. In the example above, the earliest dates for each is the first value, but this will not always be the case.

It would also be useful if it could tell me how many values are in each "mini-range" . So the first would be 3, the second 1 etc

I've been surfing google for a result, but am not having any luck. I'd really appreciate any help.

Thanks

Oh yes, I don't know if it makes a difference, but I'm running Excel 2007

zel,

You can use the following macro to accomplish what you requested:

Please Login or Register  to view this content.

Notes about the macro:

• To change which column the dates are stored in, change DateCol to the correct column letter (right now macro assumes they are in column A)
• To change which row the dates start in, change DateRow to the correct row number (right now macro assumes the dates start in row 1)

To add a macro to a workbook:

1. Save a copy of the Excel workbook you want to modify
2. Always test macros in a copy so that the original is preserved in case the modifications don't go smoothly
3. Open the copy of the Excel workbook you want to modify
4. Use the keyboard shortcut ALT+F11 to open the Visual Basic Editor
5. Insert -> Module
6. Copy/Paste the code into that area

To run a macro in a workbook:

1. In Excel 2007, go to the View tab in the ribbon
2. On the far right, click Macros
3. Double-click the desired macro (I named this one EvaluateDates)

Hope that helps,
~tigeravatar

zel,

you sample workbook had the dates, but they were text for some reason and not dates. That's what was causing the mismatch. I included in the code to format them as general and then reput the dates in so that they Excel would read them as dates. It worked successfully after that.

Updated code:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

That worked perfectly. Thank you, thank you. I've been bashing my head against this one for weeks.