formula to auto-fill data

[asma410]

Hello,
I want to know if there is any formula/method I can use in my spreadhseet that will automatically fill a column. Example, I type '1' in cell A1 & it automatically fills the (increment) values 2, 3, 4, ..... say upto 3000 in that column. I dont want to drag the values upto cell A3000 to fill the series. My last choice would be to use VBA but I was wondering if there is a direct way to do it on the spreadsheet without any Vb code.

Thanks.

 

[Sektor]

I think you must enter following formula into A2 cell and fill it till A3000. You do it once - works forever.

=A1+1

 

[kidwispa]

Try dragging down the following formula from A2:

=$A$1*row()

This will then work if you wanted 2,4,6,8...etc as well...

 

[asma410]

Thanks for the replies. I am trying to avoid dragging down or selecting the cells (even once) all the way to A3000. Is there a series fill function that will allow me to set a range (A1:A3000) in a formula & the data fills down automatically without dragging it down to A3000?

 

[Sektor]

Insert the following to ThisWorkbook module:

Private Sub Workbook_Activate()
    Call AddShortCut
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteAllMenus
End Sub

Insert the following to any standard module:

Sub AddShortCut()
    
    Dim cbr           As CommandBar
    Dim ctl           As CommandBarControl
    Dim iIndex        As Integer
    
    Set cbr = CommandBars("Cell")
    iIndex = cbr.Controls("Cut").index
    
    Set ctl = cbr.Controls.Add(Type:=msoControlButton, Before:=iIndex)
    
    With ctl
        .Caption = "Auto Fill"
        .BeginGroup = True
        .FaceId = 135
        .OnAction = "'FillDigits Selection'"
    End With

End Sub

Sub DeleteAllMenus()
    
    Dim cbr As CommandBar
    
    On Error Resume Next
    Set cbr = CommandBars("Cell")
    cbr.Controls("Auto Fill").Delete
    
    Set cbr = Nothing
    
End Sub

Sub FillDigits(rng As Range)
    rng.Offset(1, 0).Resize(4000, 1).FormulaR1C1 = "=R[-1]C+1"
End Sub

Enter any digit into cell (say, A1) and right-click it. At the top of shortcut menu you'll see "Auto Fill" button. Press it and enjoy.

 

[asma410]

Thanks