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

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

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

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