Auto fill functionality in a macro

Hi All,

You have a formula in cell b1, to the right of a column of data in column A. If you double click the bottom right corner of the cell, the formula is auto filled down column B as far as there is data in column A (as you know). I recorded this action in a macro.

Each time I run the macro column A will have varying amounts of data in it. However, when I do run it, it only fills down as far as the point WHEN I RECORDED the macro, regardless of how much data is in column A.

Does anyone know how I might get round this? Any suggestions comments appreciated.

CHEERS

Mr Grinch,

Here's something to get you started. This is probably pretty buggy, but at least you will have something to work with.


Sub AutoFill()

On Error GoTo endfunction:
i = ActiveCell.Address
e = ActiveCell.Offset(0, -1).Address
Range(e).Select
Range(Selection, Selection.End(xlDown)).Select
u = Selection.Offset(0, 1).Address
Set SourceRange = Range(i)
Set fillRange = Range(u)
SourceRange.AutoFill Destination:=fillRange
fillRange.Select
endfunction:

End Sub