Copy contents of cells down

[Dominique Feteau]

I recorded a macro that does a number of things. (filters a table, selects a
defined range, copies it into another worksheet, adjusts the column widths,
inserts 3 new columns and names them, and inserts 2 formulas in the 2nd row
of 2 of the new columns).

What I want to do i copy the formula down, but I only know how to do that if
the number of rows are static. How can I tell the macro to count how many
rows there are and copy the contents of a given cell that number of rows.
Or have it start to copy down, but have it look to the adjacent cell to see
if its empty. if its empty, then stop copying.

any solutions would be greatly appreciated.

thanks
niq

 

[Nigel]

One way is to test the extent of a column which is stored in variable this
value can then be used to set the extent of the copy.

In the example, column A on sheet1 is used to determine the last used row
(Last Row). The formula in cell B1 is copied and pasted into the rest of
column B down to the LastRow.

Dim LastRow As Long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("B1").Copy
Range("B2:B" & LastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Cheers
Nigel

 

[Tom Ogilvy]

rather than do

cells(2,2).Formula = "=some formula"

do

Dim rng as Range
With Worksheets("Destination")
set rng = .Range(.cells(1,2),.cells(rows.count,1).End(xlup))
End with
rng.offset(0,1).Formula = "=some formula"
rng.offset(0,2).formula = "=some other formula"

this will put the formula in all the cells.

 

[Jon Peltier]

Niq -

This tells me the address of the highest nonblank cell in column C:

Range("$C$65536").End(xlup).Address

This tells you the row:

Range("$C$65536").End(xlup).Row

- Jon

 

[Dominique Feteau]

Nigel

this worked like a charm. I was also able to tweek it to do a couple of
columns. I do have one question tho. How do i tweek it so it'll do an
autofill instead of just a copy and paste?

Thanks in advance
Niq

One way is to test the extent of a column which is stored in variable this
value can then be used to set the extent of the copy.

In the example, column A on sheet1 is used to determine the last used row
(Last Row). The formula in cell B1 is copied and pasted into the rest of
column B down to the LastRow.

Dim LastRow As Long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("B1").Copy
Range("B2:B" & LastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Cheers
Nigel

selects that




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---