MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replicating formulae


Posted by Colin Gough on January 08, 2002 6:01 AM

Is it possible to replicate a formula to a stated range rather than using the drag method.
i.e replicate from a1 to A2:a35000 - over a large range like this dragging the formuala is tedious


Posted by Scott on January 08, 2002 6:30 AM

Here are two quick and easy macros that you can use. Either one should work:

Sub Copy_Down()

Range("A1").Select
Selection.Copy
Range("A2:A35000").Select
ActiveSheet.Paste
End Sub


Sub Fill_Down()

Range("A1:A35000").Select
Selection.FillDown
End Sub

Posted by Mark W. on January 08, 2002 6:46 AM

1. Select cell A1
2. Type Control+G, enter A35000 in the dialog's
"Reference" field, and press Shift+Enter.
3. Click in the formula bar, and then press
Control+Enter.

Posted by Andy Gee on January 08, 2002 6:49 AM

You can also select the initial formula and hold down shift then click the destination, all the cell between will be selected then fill down / accross.

Or.

Hold control+shift and use the arrow keys to jump to the last used cell. ie If A1 to A1500 already have formulae but you want to change all of them (relatively).

Change the formula in A1, hold down control+shift press down once. Now fill down (Alt, E, I, Enter or Control+D)

I do like those macros though!

Posted by Catherine Munro on January 08, 2002 11:33 AM

If you already have data or labels in an adjacent column, you can double-click on the fill handle instead of dragging it. This will fill the formula down as far as the adjacent data extends, stopping at the first blank cell.