Copy a dynamic range

Tefleon

New Member
Joined
Sep 21, 2007
Messages
23
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0

Forum statistics

Threads
1,212,148
Messages
6,106,245
Members
448,009
Latest member
Brgrant820

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top