Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cells to variable number of rows | Excel Discussion (Misc queries) | |||
Linking to a Variable Number of Rows - XP/07 | Excel Worksheet Functions | |||
Insert Variable Number of Rows; With Loop | Excel Worksheet Functions | |||
How to copy&paste a variable range rows and colums | Excel Discussion (Misc queries) | |||
copy formulas to cells absolute rows and variable columns | Excel Discussion (Misc queries) |