If it is always going to be A to D and start in Row 2, why cite A4 to E20 in
your original post?

Nonetheless,

set rng = Range(Range("A2"),Range(A21).End(xlup)).Resize(,4)

--
Regards,
Tom Ogilvy

"ray" wrote in message
...
"Tom Ogilvy" wrote in
:

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

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


They are a contiguos rectangle. The range will only grow in number of
rows,
not columns. Just need to be able to have a routine that always starts in
a
cell and then determines the end of range based on occupied cells.

Date Data1 Data2 Data3
1103 1 2 3
2103 4 5 6

Assuming the data(minus headings) starts in A2, then the range of this
would be A2:D3. I need a routine to automagically determine that.

Ray