Fill Series Function

[smacinnis]

I need to fill a column with consecutive numbers linked to cells with a beginning and end value. Does anyone know of function to automate this process? For example in cell B1, I would type the number 1 and in cell C1, I type the number 10. Then the function would automically fill in column A from 1 to 10. If I were to change the end value to 15 then coulmn A would automatically extend to the value 15.

 

[kskinne]

you can do this with VBA code, just right click on the sheet tab of the sheet you are working in, select 'view code', and paste the following code in the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Dim x As Long
x = Target.Value
For i = 1 To x
Cells(i, 1).Value = i
Next i
End Sub

then go back to your sheet and type a 1 in cell B1 and any ending value in cell C1. your values in column A should automatically fill in.

hth
kevin

 

[rrdonutz]

Kevin,

I would modify your code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Dim x As Long
x = Target.Value
[A:A].ClearContents
For i = 1 To x
Cells(i, 1).Value = i
Next i
End Sub

The ClearContents line insures that if C1 changes from 15 to 10, for example, then cells A11 through A15 do not remain valued at 11 through 15.

 

[rrdonutz]

Here is an additional code modification that allows any number to be entered in B1 (not just 1), and caps the number of cells at 65,536:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" And Target.Address <> "$C$1" Then Exit Sub
Dim x As Long, y As Long, z As Long
x = [B1] - 1
y = [C1]
z = y - x
[A:A].ClearContents
If z < 0 Then Exit Sub
If z > 65536 Then z = 65536
For i = 1 To z
Cells(i, 1) = x + i
Next i
End Sub