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

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

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

How to transfer the length of one column to a formula in Visua
 

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

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

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

How to transfer the length of one column to a formula in Visua
 

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

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

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" wrote in message
...
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