Return Range in VB

  • Thread starter Thread starter ray
  • Start date Start date
R

ray

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
 
Unless the populated cells are a contiguous rectangle, you won't be able to
paste transposed

set rng = Range("A4:E20").SpecialCells(xlConstants)
 
Unless the populated cells are a contiguous rectangle, you won't be
able to paste transposed

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

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
 
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)
 
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)
Click to expand...

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