Copy Paste a variable number of rows 1

[Tiglet]

Hi,

I have a spreadsheet with rows of data, I would like to insert a column, ("H:H&quot, enter a formula in ("H1&quot, then copy and paste that formula down all of the rows that contain data in other columns. The problem is that the number of rows is different in each spreadsheet so the bottom row in the paste area needs to be a variable.

I have a macro which will find the first blank cell in a column which would work but I'm hoping there is a far more efficient way of doing it.I have recorded a macro to play around with various key strokes such as Ctrl Shift and down arrow etc. but it doesn't seem to work.

Thanks in advance

 

[BENC]

Try using the CurrentRegion property. If you know the address of a cell in the data, CurrentRegion will find all the cells that adjoin it (try Shift-Ctrl-8 in a spreadsheet to see the current region). You can then use rows.count to get the number of rows in the current region, for copying your formula.

 

[DomThePom]

No send keys please!

You need to use the end property of the range object - help is as follows-:

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only.

Syntax

expression.End(Direction)

expression Required. An expression that returns a Range object.

Direction Required Long. The direction in which to move. Can be one of the following XlDirection constants: xlToLeft, xlToRight, xlUp, or xlDown.


So - say you had a column of data starting in cell B4 and you wanted to define an adjacent range in column C the VBA would be:

set rng = range("B4", Range("B4&quot.End(xlDown)).offset(0,1)

Have fun

 

[Tiglet]

DomThePom

Thanks

Works a treat, have a star!



Tiglet

[green]Duct tape is like the force; it has a light side & a dark side, and it holds the universe together. [/green]