Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill down until reach variable range
I need to number a form using autofill, the start cell is static but the end
cell is a variable. I have written the following code but I can't figure out how to refer to the varable when autofilling. The code selects the entire range but I can't figure out how to autofill from c15:LastCell. Thank you for your help. Sub num2() ' ' num2 Macro ' Macro recorded 10/23/2009 by tpeter ' Dim LastCell As Range Set LastCell = Range("C15").End(xlDown) Range("C15").Select ActiveCell.FormulaR1C1 = "1" Range("C16").Select ActiveCell.FormulaR1C1 = "2" Range("C15:C16").Select Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault Range("C15:C28").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill down until reach variable range
I usually like to pick a column that has data in it if the row is used. In this
code, I used column A. Option Explicit Sub num2() Dim LastRow As Long Dim wks As Worksheet Set wks = ActiveSheet With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("C15").Value = 1 .Range("C16").Value = 2 .Range("c15:C16").AutoFill _ Destination:=.Range("C15:c" & LastRow), _ Type:=xlFillDefault End With End Sub tpeter wrote: I need to number a form using autofill, the start cell is static but the end cell is a variable. I have written the following code but I can't figure out how to refer to the varable when autofilling. The code selects the entire range but I can't figure out how to autofill from c15:LastCell. Thank you for your help. Sub num2() ' ' num2 Macro ' Macro recorded 10/23/2009 by tpeter ' Dim LastCell As Range Set LastCell = Range("C15").End(xlDown) Range("C15").Select ActiveCell.FormulaR1C1 = "1" Range("C16").Select ActiveCell.FormulaR1C1 = "2" Range("C15:C16").Select Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault Range("C15:C28").Select End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill down until reach variable range
Try
Sub Macro1() Dim lngLastRow As Long lngLastRow = Range("C15").End(xlDown).Row Range("C15") = 1 Range("C16") = 2 Range("C15:C16").AutoFill Destination:=Range("C15:C" & _ lngLastRow), Type:=xlFillDefault End Sub If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: I need to number a form using autofill, the start cell is static but the end cell is a variable. I have written the following code but I can't figure out how to refer to the varable when autofilling. The code selects the entire range but I can't figure out how to autofill from c15:LastCell. Thank you for your help. Sub num2() ' ' num2 Macro ' Macro recorded 10/23/2009 by tpeter ' Dim LastCell As Range Set LastCell = Range("C15").End(xlDown) Range("C15").Select ActiveCell.FormulaR1C1 = "1" Range("C16").Select ActiveCell.FormulaR1C1 = "2" Range("C15:C16").Select Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault Range("C15:C28").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofill down until reach variable range
Worked Great Thanks for all of your help. Just a comment to anyone looking at
this,if there is no information in column c this will fill all the way down. "Jacob Skaria" wrote: Try Sub Macro1() Dim lngLastRow As Long lngLastRow = Range("C15").End(xlDown).Row Range("C15") = 1 Range("C16") = 2 Range("C15:C16").AutoFill Destination:=Range("C15:C" & _ lngLastRow), Type:=xlFillDefault End Sub If this post helps click Yes --------------- Jacob Skaria "tpeter" wrote: I need to number a form using autofill, the start cell is static but the end cell is a variable. I have written the following code but I can't figure out how to refer to the varable when autofilling. The code selects the entire range but I can't figure out how to autofill from c15:LastCell. Thank you for your help. Sub num2() ' ' num2 Macro ' Macro recorded 10/23/2009 by tpeter ' Dim LastCell As Range Set LastCell = Range("C15").End(xlDown) Range("C15").Select ActiveCell.FormulaR1C1 = "1" Range("C16").Select ActiveCell.FormulaR1C1 = "2" Range("C15:C16").Select Selection.AutoFill Destination:=Range("C15:C30"), Type:=xlFillDefault Range("C15:C28").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill with Column as a variable | Excel Programming | |||
Autofill variable range | Excel Programming | |||
I need to add a range of cells until I reach a cell with a zero va | Excel Worksheet Functions | |||
Variable Autofill range | Excel Programming | |||
Variable Autofill Range | Excel Programming |