Copying a formula in a blank column as far as data in previous column

Hi

I wish to automate a formula that I have for removing duplicate telephone numbers.

When recording a macro, I want to paste the formula as far down as the last number (the quantity of numbers to deduplicate will usually be different each time ) which is situated in the previous column.

Is there a way that I can achieve this, rather than having to paste down the whole column?

Many Thanks

assume you are pasting in column C and using column B as a reference

Dim rng as Range
Set rng = Range(Range("B1"),Range("B1").End(xldown))
set rng = rng.offset(0,1)
rng(1).copy Destination:=rng

--
Regards,
Tom Ogilvy

"basildon" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> I wish to automate a formula that I have for removing duplicate
> telephone numbers.
>
> When recording a macro, I want to paste the formula as far down as the
> last number (the quantity of numbers to deduplicate will usually be
> different each time ) which is situated in the previous column.
>
> Is there a way that I can achieve this, rather than having to paste
> down the whole column?
>
> Many Thanks
>
>
> --
> basildon
> ------------------------------------------------------------------------
> basildon's Profile:
http://www.excelforum.com/member.php...o&userid=29700
> View this thread: http://www.excelforum.com/showthread...hreadid=494135
>