Fill emply column with formula
 

I have entered a formula into M2. Now I want to fill it to the last column
which has data in it. When I double-click on the fill handle, I get what I
want.

However, when I record the action as a macro, I get:

Selection.AutoFill Destination:=Range("M2:M3085")

I don't want the range hard-coded, because the next time I use the macro the
number of rows will be different. When I change the range to
M2...end(xldown), I get M2:M65536.

How do I specify a range which has the same effect as double-clicking on the
fill handle?

--
Thanks,
Fred
Please reply to newsgroup, not e-mail

Fill emply column with formula
 

Selection.AutoFill Destination:=Range("M2", _
Cells(rows.count,"L").End(xlup).Offset(0,1))

--
Regards,
Tom Ogilvy

Fred Smith wrote in message
...
I have entered a formula into M2. Now I want to fill it to the last column
which has data in it. When I double-click on the fill handle, I get what I
want.

However, when I record the action as a macro, I get:

Selection.AutoFill Destination:=Range("M2:M3085")

I don't want the range hard-coded, because the next time I use the macro
the
number of rows will be different. When I change the range to
M2...end(xldown), I get M2:M65536.

How do I specify a range which has the same effect as double-clicking on
the
fill handle?

--
Thanks,
Fred
Please reply to newsgroup, not e-mail

Fill emply column with formula
 

Fred

Assuming Column L is the adjacent column with data.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("L" & Rows.Count).End(xlUp).Row
Range("M2:M" & Lrow).FillDown
End With
End Sub

Gord Dibben Excel MVP

On Wed, 4 Feb 2004 21:32:34 -0600, "Fred Smith" wrote:

I have entered a formula into M2. Now I want to fill it to the last column
which has data in it. When I double-click on the fill handle, I get what I
want.

However, when I record the action as a macro, I get:

Selection.AutoFill Destination:=Range("M2:M3085")

I don't want the range hard-coded, because the next time I use the macro the
number of rows will be different. When I change the range to
M2...end(xldown), I get M2:M65536.

How do I specify a range which has the same effect as double-clicking on the
fill handle?