Representing autofill in macros

  • Thread starter Thread starter luke
  • Start date Start date
L

luke

I have recorded number of Excel actions as a macro. One
of the actions is the as follows:

The sheet already has a number of rows of data in column
B.
In cell A1 is a formula, which I want to copy down as far
as the data in column B.
I do this either by selecting cell A1, moving the cursor
to the bottom right- hand corner, and clicking on the
cross that appears. I could also do this by copying the
formula, holding down 'Shift', 'End' and the down-arrow on
the keyboard and pasting the formula.

However, the macro records this action as copying/pasting
to a set range. If I run the macro when there is a
different number of rows of data in column B, the macro
only copies down as far as it did when the macro was
recorded.

How can I get the macro to copy down as far as the data in
column B regardless of the number of rows?

Thanks.
 
Correction:
Sub CopyFormula()
For a = 1 To Range("B1").CurrentRegion.Rows.Count
Cells(a, 1).FormulaR1C1 = Cells(1, 1).FormulaR1C1
Next a
End Sub
 
Back
Top