Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Flexible autofill in macro
Hi. I have a macro, that adds in two columns a value "Bestellijst" and "CTR". After that I want the macro to autofill these values for every row in the spreadsheet. The number of rows vary. It could be 10, 346, 4659 or any other number. When first recording the macro it stored the exact range at the time... somthing like "C2:D3476" for this autofill. I changed it already into the code below. The problem now however... is that it autofills until the last row excell can handle. How can I get it to autofill only until the row with the last record each time I run the macro, independant of the number of records? ---code--- Range("C2").Select ActiveCell.FormulaR1C1 = "Bestellijst" Range("D2").Select ActiveCell.FormulaR1C1 = "CTR" Range("C2:D2").Select Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)) ---/code--- Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Flexible autofill in macro
Hi I suppose you have data in column A, so we can use this column to determine LastRow. Also I removed the "Select" statements as they are not needed, and will slow down your macro: Dim LastRow As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("C2").FormulaR1C1 = "Bestellijst" Range("D2").FormulaR1C1 = "CTR" Range("C2:D2").AutoFill Destination:=Range("C2:D" & LastRow) Hopes this helps. .... Per "Pluggie" skrev i meddelelsen ... Hi. I have a macro, that adds in two columns a value "Bestellijst" and "CTR". After that I want the macro to autofill these values for every row in the spreadsheet. The number of rows vary. It could be 10, 346, 4659 or any other number. When first recording the macro it stored the exact range at the time... somthing like "C2:D3476" for this autofill. I changed it already into the code below. The problem now however... is that it autofills until the last row excell can handle. How can I get it to autofill only until the row with the last record each time I run the macro, independant of the number of records? ---code--- Range("C2").Select ActiveCell.FormulaR1C1 = "Bestellijst" Range("D2").Select ActiveCell.FormulaR1C1 = "CTR" Range("C2:D2").Select Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)) ---/code--- Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Flexible autofill in macro
Yes it did... Thanks a lot.
"Per Jessen" wrote: Hi I suppose you have data in column A, so we can use this column to determine LastRow. Also I removed the "Select" statements as they are not needed, and will slow down your macro: Dim LastRow As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("C2").FormulaR1C1 = "Bestellijst" Range("D2").FormulaR1C1 = "CTR" Range("C2:D2").AutoFill Destination:=Range("C2:D" & LastRow) Hopes this helps. .... Per "Pluggie" skrev i meddelelsen ... Hi. I have a macro, that adds in two columns a value "Bestellijst" and "CTR". After that I want the macro to autofill these values for every row in the spreadsheet. The number of rows vary. It could be 10, 346, 4659 or any other number. When first recording the macro it stored the exact range at the time... somthing like "C2:D3476" for this autofill. I changed it already into the code below. The problem now however... is that it autofills until the last row excell can handle. How can I get it to autofill only until the row with the last record each time I run the macro, independant of the number of records? ---code--- Range("C2").Select ActiveCell.FormulaR1C1 = "Bestellijst" Range("D2").Select ActiveCell.FormulaR1C1 = "CTR" Range("C2:D2").Select Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)) ---/code--- Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flexible X axis VBA/Macro generated | Charts and Charting in Excel | |||
Autofill in macro | Excel Discussion (Misc queries) | |||
How to call a macro with a flexible name | Excel Programming | |||
Making the macro more flexible? | Excel Programming | |||
VBA - Flexible Sort Macro | Excel Programming |