macro to Fill series till last selected cells

[hajiali]

Hello,
I need to accomplish to get a macro that will auto fill series on the selected range in a column. the first selected cell in the range will always be a number. and selection will always the in column A. however, the selected range will not always be the same.

ex1: if cell A5 = 1 and if users selects range A5:A10 the press the macro results should be the following:
A6=2
A7=3
A8=4
A9=5
A10=6

the macro counts till the last selected row.

Ex2 if Cell A50=67 and user selects range A50:A66 and press the macro it should give the following:
A51=68
A52=69
A53=70
A54=71
A55=72....ETC

and fill till the last cell selected in this case A66=83.

 

[footoo]

With Selection
    .Value = Evaluate("Row(" & .Address & ")+" & .Item(1) - .Row())
End With

 

[Trebor76]

Hi hajiali,

Try this:

Option Explicit
Sub Macro1()

    Application.ScreenUpdating = False
    
    On Error Resume Next
        With Selection.Resize(Selection.Rows.Count - 1).Offset(1, 0)
            If Err.Number = 0 Then
                .Formula = "=" & Cells(Selection.Row, Selection.Column).Address(False, False) & "+1"
                .Value = .Value
            Else
                MsgBox "There is no range selected.", vbExclamation
            End If
        End With
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Peter_SSs]

Try:

Sub FillIt()
    Selection.DataSeries
End Sub

 

[hajiali]

Thank you all for the quick response. All of them work Great. Thanks

 

[hajiali]

Trebor76 using your code in post #3 how would I add functional to skip over the hidden rows. Not skipping the numbering but skip the cell
EX. if Cell A50=67 and user selects range A50:A66 and say rows A52 and A55 are Hidden and user press the macro that the results would be.

A51=68
A52=
A53=69
A54=70
A55=
A56=71
A57=72
A58=73 ETC to the last selected.

Cells A52 and A55 would have other number and hence the user might want to hide them in order to reduce the remaining options to select from

 

[Trebor76]

There maybe a more efficient way (happy to see them ) but as native Excel formulas do not handle hidden rows well you can use this:

Option Explicit
Sub Macro2()      
    
    Dim rngMyCell As Range, rngSelection As Range
    Dim dblMyNum As Double
    
    On Error Resume Next
        Set rngSelection = Selection.Resize(Selection.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        If Err.Number <> 0 Then
            MsgBox "There is no range selected.", vbExclamation
            Exit Sub
        End If
    On Error GoTo 0
    
    Application.ScreenUpdating = False
   
    dblMyNum = CDbl(Selection.Item(1))
    For Each rngMyCell In rngSelection
        dblMyNum = dblMyNum + 1
        rngMyCell.Value = dblMyNum
    Next rngMyCell
        
    Application.ScreenUpdating = True
    
End Sub

For small data sets like you've been suggesting it is fine.

Thanks,

Robert

 

[Peter_SSs]

Thank you all for the quick response. All of them work Great. Thanks

You're welcome. Glad we could help.

how would I add functional to skip over the hidden rows.

For my code it would be ..

Sub FillIt_v2()
    Selection.SpecialCells(xlVisible).DataSeries
End Sub

 

[Trebor76]

Hi Peter,

Gear solution as always

Hi hajiali,

Unless there's a really good reason not to (which I can't think of any), I would Peter's superior non-looping solution over mine.

Regards,

Robert

 

[hajiali]

Thanks Peter_SSs Im getting the following Error Message