copy down with variable number of rows
 

Hi,

I would like to add a copy down function into a macro but the number
of rows varies.

I insert 3 columns A to C and add formulas in the first 3 cells
(A1:C1). These formulas need to be copied down as long as there are
values in colums D (= to the first blank cell in D). Like the auto
fill function.

How can I do this?

With the macro recorder using the autofill it gives me a fixed range.

Cheers,

Harold

copy down with variable number of rows
 

Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("a1:c1").AutoFill _
Destination:=.Range("A1:C" & LastRow), Type:=xlFillDefault
End With


But you may find that you can just populate the formulas in each column:

Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("a1:A" & lastrow).formula = "=formulaforA1Here"
.Range("b1:b" & lastrow).formula = "=formulaforb1Here"
.Range("c1:c" & lastrow).formula = "=formulaforc1Here"
End With




mohavv wrote:

Hi,

I would like to add a copy down function into a macro but the number
of rows varies.

I insert 3 columns A to C and add formulas in the first 3 cells
(A1:C1). These formulas need to be copied down as long as there are
values in colums D (= to the first blank cell in D). Like the auto
fill function.

How can I do this?

With the macro recorder using the autofill it gives me a fixed range.

Cheers,

Harold

--

Dave Peterson

copy down with variable number of rows
 

To first blank cell in D or to the last filled cell in D?

This macro will fill to last filled cell in D.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("A1:C" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

On 14 Nov 2007 14:08:14 -0800, mohavv wrote:

Hi,

I would like to add a copy down function into a macro but the number
of rows varies.

I insert 3 columns A to C and add formulas in the first 3 cells
(A1:C1). These formulas need to be copied down as long as there are
values in colums D (= to the first blank cell in D). Like the auto
fill function.

How can I do this?

With the macro recorder using the autofill it gives me a fixed range.

Cheers,

Harold

copy down with variable number of rows
 

Just a warning (that won't matter this time <bg).

You forgot your dots in front of a couple of range()'s.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("A1:C" & Lrow).FillDown
End With
End Sub

To the OP: Since the code should be in a General module, those unqualifed
ranges will refer to the activesheet. And Gord's code wanted to use the
ActiveSheet anyway.

Gord Dibben wrote:

To first blank cell in D or to the last filled cell in D?

This macro will fill to last filled cell in D.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("A1:C" & Lrow).FillDown
End With
End Sub

Gord Dibben MS Excel MVP

On 14 Nov 2007 14:08:14 -0800, mohavv wrote:

Hi,

I would like to add a copy down function into a macro but the number
of rows varies.

I insert 3 columns A to C and add formulas in the first 3 cells
(A1:C1). These formulas need to be copied down as long as there are
values in colums D (= to the first blank cell in D). Like the auto
fill function.

How can I do this?

With the macro recorder using the autofill it gives me a fixed range.

Cheers,

Harold

--

Dave Peterson

copy down with variable number of rows
 

Thanks Dave

I am going to speak to those quality control people about whether they want to
continue working for me or find employment elsewhere.


Gord

On Wed, 14 Nov 2007 19:01:28 -0600, Dave Peterson
wrote:

Just a warning (that won't matter this time <bg).

You forgot your dots in front of a couple of range()'s.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("A1:C" & Lrow).FillDown
End With
End Sub

To the OP: Since the code should be in a General module, those unqualifed
ranges will refer to the activesheet. And Gord's code wanted to use the
ActiveSheet anyway.

Gord Dibben wrote:

To first blank cell in D or to the last filled cell in D?

This macro will fill to last filled cell in D.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("A1:C" & Lrow).FillDown
End With
End Sub

Gord Dibben MS Excel MVP

On 14 Nov 2007 14:08:14 -0800, mohavv wrote:

Hi,

I would like to add a copy down function into a macro but the number
of rows varies.

I insert 3 columns A to C and add formulas in the first 3 cells
(A1:C1). These formulas need to be copied down as long as there are
values in colums D (= to the first blank cell in D). Like the auto
fill function.

How can I do this?

With the macro recorder using the autofill it gives me a fixed range.

Cheers,

Harold

copy down with variable number of rows
 

You sure it wasn't a bad keyboard <bg?

That's what I'd blame.

Gord Dibben wrote:

Thanks Dave

I am going to speak to those quality control people about whether they want to
continue working for me or find employment elsewhere.

Gord

On Wed, 14 Nov 2007 19:01:28 -0600, Dave Peterson
wrote:

Just a warning (that won't matter this time <bg).

You forgot your dots in front of a couple of range()'s.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("A1:C" & Lrow).FillDown
End With
End Sub

To the OP: Since the code should be in a General module, those unqualifed
ranges will refer to the activesheet. And Gord's code wanted to use the
ActiveSheet anyway.

Gord Dibben wrote:

To first blank cell in D or to the last filled cell in D?

This macro will fill to last filled cell in D.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("A1:C" & Lrow).FillDown
End With
End Sub

Gord Dibben MS Excel MVP

On 14 Nov 2007 14:08:14 -0800, mohavv wrote:

Hi,

I would like to add a copy down function into a macro but the number
of rows varies.

I insert 3 columns A to C and add formulas in the first 3 cells
(A1:C1). These formulas need to be copied down as long as there are
values in colums D (= to the first blank cell in D). Like the auto
fill function.

How can I do this?

With the macro recorder using the autofill it gives me a fixed range.

Cheers,

Harold

--

Dave Peterson