Thanks Jon, just the job. I'll look at the other suggestion of a dynamic
range, see if I can figure it out!
Regards, Rob
"Jon Peltier" wrote in message
...
You can shorten it a lot:
ActiveCell.FormulaR1C1 = _
"=""Dates: ""&TEXT(MIN(R[3]C:R[65000]C),""dd mmm yyyy"")&" _
& """ to ""&TEXT(MAX(R[3]C:R[65000]C),""dd mmm yyyy"")"
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Rob" wrote in message
...
Using Excel 2000. The below lines are on two rows, I'd like to have as a
few more rows so it's easier to read albeit the _ (space underscore)
doesn't like breaking the lines. Is this possible?
Also, I don't really like the [65000] in the code but I don't know any
other way to get the MIN and MAX dates from a range that charges
frequently. The below code places in a file then range values.
ActiveCell.FormulaR1C1 = _
"=""Dates: ""&TEXT(MIN(R[3]C:R[65000]C),""dd"")&""
""&TEXT(MIN(R[3]C:R[65000]C),""mmm"")&""
""&TEXT(MIN(R[3]C:R[65000]C),""yyyy"")&"" to
""&TEXT(MAX(R[3]C:R[65000]C),""dd"")&""
""&TEXT(MAX(R[3]C:R[65000]C),""mmm"")&""
""&TEXT(MAX(R[3]C:R[65000]C),""yyyy"")"
Thanks, Rob