Copying a range that changes...

[scotthannaford1973]

Hi

I have a table, lets say columns A to D on "Sheet 1" and I want to be able to select the table, copy it and paste it elsewhere. The only proviso is that as the table gets data from a pivot table, the length of the table changes - sometimes it's A1:D50 and the next day it may be A1:D47 or A1:D100 etc

I'd like to just be able to select the table as far as the bottom row where A is populated - so if A1:A50 have content but A51 onwards is blank, I want to be able to select A1:D50

The rest of the copy and paste VB is fine, but I have no idea how to select "only the populated part of the table"...

any help would be much appreciated!

 

[Joe4]

See if this dynamically selects all the data you want:

Range("A1").CurrentRegion.Copy

 

[scotthannaford1973]

See if this dynamically selects all the data you want:

Range("A1").CurrentRegion.Copy

Thanks - that's spot on - wasn't aware of "range"!

 

[Joe4]

Thanks - that's spot on - wasn't aware of "range"!

You are welcome.

"Range" is not the key part here, "CurrentRegion" is. "CurrentRegion" is the same if you hit "CTRL+SHIFT+*" on your keyboard.
Basically, from whatever cell you start from Current Region will select the Contiguous Range from that cell.
So it will go down as far as the first entirely blank row, to the right as far as the first entirely blank column, and then the same for up and to the left.

 

[scotthannaford1973]

lol sorry yes - my typo! I meant "currentregion" was new to me! it's been a long week

 

[Joe4]

No worries!