Unless the populated cells are a contiguous rectangle, you won't be able to
paste transposed

set rng = Range("A4:E20").SpecialCells(xlConstants)

--
Regards,
Tom Ogilvy

"ray" wrote in message
.. .
This is going to be a noobie type question, but I really need some help. I
want to be able to determine the range of cells that are populated with
values(ie A4:E20) using VB. I will then take this range, copy it and paste
it transposed to another sheet. I have the second part done, just can't
quite get the part about returning the range on a sheet dynamically based
on the cells with values. The sheet will be updated regularly and I don't
want to have to change the code for the populated range each time.

I hope this makes sense.

Thanks
Ray