See in-line

On Wed, 12 Dec 2007 14:16:01 -0800, Steve
wrote:

Ron de Bruins code to merge cells is very helpful. I'm trying to modify his
code to "Merge cells from all or some worksheets into one Master sheet". How
do you make the range you're copying FROM dynamic? Let's say I want to start
in C3 and want to include to column J but the last row may not be the same in
each sheet? So I want the range C3..J?? How do I do that?

Set rng = Range(Range("C3"), Cells(Rows.Count, "J").End(xlUp))


Second question...what if I'm not sure what the last column is? Is there a
function that returns the last cell in usedrange?

If data is contiguous you can use currentregion

Set rng = Range("C3").CurrentRegion

To return the address of last cell in currentregion

Set rng = Range("C3").CurrentRegion
MsgBox rng(rng.Count).Address


Thanks.



Gord Dibben MS Excel MVP