Return Range in VB
 

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

Return Range in VB
 

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

Return Range in VB
 

"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

Return Range in VB
 

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

Return Range in VB
 

"Tom Ogilvy" wrote in
:

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)


Because I lost track of my example. Should have been A to E as indicated in
original post. Thanks