Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 68
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy cells to variable number of rows Acct Supr - DCTC Excel Discussion (Misc queries) 5 September 26th 09 12:58 PM
Linking to a Variable Number of Rows - XP/07 RFJ Excel Worksheet Functions 4 May 17th 07 07:53 PM
Insert Variable Number of Rows; With Loop ryguy7272 Excel Worksheet Functions 2 December 27th 06 09:25 PM
How to copy&paste a variable range rows and colums IK Excel Discussion (Misc queries) 1 August 30th 06 12:06 AM
copy formulas to cells absolute rows and variable columns Mark Excel Discussion (Misc queries) 1 January 30th 06 11:15 PM


All times are GMT +1. The time now is 12:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"