Copy a dynamic range

[Tefleon]

Hi

I'm stuck, very stuck.

Using Excel 2007 -

I have a table which I complete and copy to a separate part my my sheet. The table is a fixed size (7x7) and contains blank cells for both formatting and null calculations.

As I work, I end up with several of these tables copied in a column and when I'm finished, I want to select all of them and paste them to a word document.

How do I create a dynamic selection area which takes into account the number of blanks. I do know how many tables I have to copy, just not how to include this in the range selection.

One table to copy would be A1:E7
Two tables to copy would be A1:E15 - As I include a blank row between the tables.
Three tables to copy would be A1:E24 - and so on.

Please help

 

[JLGWhiz]

Hi

I'm stuck, very stuck.

Using Excel 2007 -

I have a table which I complete and copy to a separate part my my sheet. The table is a fixed size (7x7) and contains blank cells for both formatting and null calculations.

As I work, I end up with several of these tables copied in a column and when I'm finished, I want to select all of them and paste them to a word document.

How do I create a dynamic selection area which takes into account the number of blanks. I do know how many tables I have to copy, just not how to include this in the range selection.

One table to copy would be A1:E7
Two tables to copy would be A1:E15 - As I include a blank row between the tables.
Three tables to copy would be A1:E24 - and so on.

Please help

This solution assumes that there will be no data in rows below the dynamic section.
Pick a column within the group of columns where you create the dynamic section that will always have data in the last row. There might be more than one, but you only need one for this purpose.
Now you will find that last row in the range with VBA:
Assumptions: Dynamic range columns are fixed A:G or seven columns. Worksheet is Sheet1. VBA is used to create the dynamic range.
Code:
Dim sh as Worksheet, lr as Long, rng As Range
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, "A").End(xlUp).Row 'This finds the last row in the range
Set rng = sh.Range("A1:G" & lr) 'This sets the dynamic range to a variable.
Code:
The Dim statement would go at the top of your procedure along with your other declarations. The Set statement for the sheet can go at the top of the procedure following the declarations, but the variable assignment for the last row (lr) and the Set rng statements will need to be inserted at a point in your code after the dynamic range is area is filled. Otherwise, the last row value would be erroneous and the process would fail. But if properly placed in the code, you would then use it like:
Code:
rng.Copy Sheets("somesheet").Range("A1")
Code:
You do not need to select the range to copy it. The rng variable will include all of the dynamic area no matter what size, so long as it is in the columns specified.