How to program double click of fill handle

I want to do in VBA the equivalent of double-clicking on the fill handle.

When I record the macro, I get a hard coded range, as in:

Selection.AutoFill Destination:=Range("B2:B15")

I want the range to be a variable length, ie, however many contiguous rows are
in column A. I tried:

Range("B2", Range("A2").end(xldown).offset(0,1))

but I got a two column range out of this rather than one. What range will solve
my problem?

--
Thanks,
Fred

Hi Fred,
Fill-Handle, Replication and use of the Mouse
Filldown as a Macro solution (#filld)
http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld

pretty close approximation, the double-click will use the other
side if needed, but normally is based on the left side.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Fred Smith" <[email protected]> wrote in message news:[email protected]...
> I want to do in VBA the equivalent of double-clicking on the fill handle.
>
> When I record the macro, I get a hard coded range, as in:
>
> Selection.AutoFill Destination:=Range("B2:B15")
>
> I want the range to be a variable length, ie, however many contiguous rows are
> in column A. I tried:
>
> Range("B2", Range("A2").end(xldown).offset(0,1))
>
> but I got a two column range out of this rather than one. What range will solve
> my problem?
>
> --
> Thanks,
> Fred
>
>
>

Thanks David. Just what I needed.

--
Regards,
Fred


"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> Hi Fred,
> Fill-Handle, Replication and use of the Mouse
> Filldown as a Macro solution (#filld)
> http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld
>
> pretty close approximation, the double-click will use the other
> side if needed, but normally is based on the left side.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Fred Smith" <[email protected]> wrote in message
> news:[email protected]...
>> I want to do in VBA the equivalent of double-clicking on the fill handle.
>>
>> When I record the macro, I get a hard coded range, as in:
>>
>> Selection.AutoFill Destination:=Range("B2:B15")
>>
>> I want the range to be a variable length, ie, however many contiguous rows
>> are
>> in column A. I tried:
>>
>> Range("B2", Range("A2").end(xldown).offset(0,1))
>>
>> but I got a two column range out of this rather than one. What range will
>> solve
>> my problem?
>>
>> --
>> Thanks,
>> Fred
>>
>>
>>
>
>