How to transfer the length of one column to a formula in Visual ba

I have a column A of variable length (say 100 cells). I have a formula in J2
which I wish to pull down for that number of cells.

Range ("J2").Select
Selection.Autofill Destination:=Range("J2:J101"), Type:=xlFillDefault.

How do I get the length of Column A to replace the term 'J101' in this
formula?

Thanks

You could replace you two lines of code with this.

Range("J2").AutoFill Destination:=Range("J2", Cells(Rows.Count, 1).End(xlUp).Offset(0, 9)), Type:=xlFillDefault

Cheers!

Selection.AutoFill Destination:=Range("j2:j"
&ActiveSheet.UsedRange.Rows.Count)
"Kanga 85" wrote:

> I have a column A of variable length (say 100 cells). I have a formula in J2
> which I wish to pull down for that number of cells.
>
> Range ("J2").Select
> Selection.Autofill Destination:=Range("J2:J101"), Type:=xlFillDefault.
>
> How do I get the length of Column A to replace the term 'J101' in this
> formula?
>
> Thanks

Thanks.
This works alright except that I have further data in cells J102-J106 which
now get overwritten. I only want to fill the cells in Column J which
correspond to the number of cells I have in Column A, no more and no less.

"DMoney" wrote:

>
> Selection.AutoFill Destination:=Range("j2:j"
> &ActiveSheet.UsedRange.Rows.Count)
> "Kanga 85" wrote:
>
> > I have a column A of variable length (say 100 cells). I have a formula in J2
> > which I wish to pull down for that number of cells.
> >
> > Range ("J2").Select
> > Selection.Autofill Destination:=Range("J2:J101"), Type:=xlFillDefault.
> >
> > How do I get the length of Column A to replace the term 'J101' in this
> > formula?
> >
> > Thanks

Kanga 85,

Did you try my suggestion? This finds the last used cell in Col 1 (Col A) and copies your formula down to the corresponding row in Col J.

dim LastRow as long

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("j2").autofill _
destination:=.range("J2:J" & lastrow), type:=xlfilldefault
end with



Kanga 85 wrote:
>
> I have a column A of variable length (say 100 cells). I have a formula in J2
> which I wish to pull down for that number of cells.
>
> Range ("J2").Select
> Selection.Autofill Destination:=Range("J2:J101"), Type:=xlFillDefault.
>
> How do I get the length of Column A to replace the term 'J101' in this
> formula?
>
> Thanks

--

Dave Peterson

Thanks Dave; running well

"Dave Peterson" wrote:

> dim LastRow as long
>
> with activesheet
> lastrow = .cells(.rows.count,"A").end(xlup).row
> .range("j2").autofill _
> destination:=.range("J2:J" & lastrow), type:=xlfilldefault
> end with
>
>
>
> Kanga 85 wrote:
> >
> > I have a column A of variable length (say 100 cells). I have a formula in J2
> > which I wish to pull down for that number of cells.
> >
> > Range ("J2").Select
> > Selection.Autofill Destination:=Range("J2:J101"), Type:=xlFillDefault.
> >
> > How do I get the length of Column A to replace the term 'J101' in this
> > formula?
> >
> > Thanks
>
> --
>
> Dave Peterson
>

Here's a slightly different approach similar to Dave's excellent idea.

Sub Demo()
With Range([A2], Cells(Rows.Count, "A").End(xlUp))
[J2].Resize(.Rows.Count).FillDown
End With
End Sub

--
Dana DeLouis
Win XP & Office 2003


"Kanga 85" <Kanga85@discussions.microsoft.com> wrote in message
news:08834F8F-6BBD-4890-B2A3-871A7C8542F7@microsoft.com...
>I have a column A of variable length (say 100 cells). I have a formula in
>J2
> which I wish to pull down for that number of cells.
>
> Range ("J2").Select
> Selection.Autofill Destination:=Range("J2:J101"), Type:=xlFillDefault.
>
> How do I get the length of Column A to replace the term 'J101' in this
> formula?
>
> Thanks