Shifting cell down with formula

[kamranyd]

these codes shift one cell down, whereas i want first 3 cells shift down and fill, rest cells shift down but don't fill, and can we make these codes more short?

Private Sub add_row_list_Click()
With Selection
        ActiveCell.Offset(1).Resize(1, 9).Insert shift:=xlDown
        ActiveCell.Offset(1, 1).FillDown
        ActiveCell.Offset(1, 2).FillDown
        ActiveCell.Offset(1, 3).FillDown
        ActiveCell.Offset(1, 4).FillDown
        ActiveCell.Offset(1, 5).FillDown
        ActiveCell.Offset(1, 6).FillDown
        ActiveCell.Offset(1, 7).FillDown
        ActiveCell.Offset(1, 8).FillDown
        ActiveCell.Offset(1, 0).Select
End With
End Sub

 

[Cubist]

How about this?

Private Sub add_row_list_Click()
    With Selection
        .Offset(1).Resize(1, 9).Insert shift:=xlDown
        .Offset(1, 1).Resize(, 8).FillDown
        .Offset(1, 0).Select
    End With
End Sub

 

[rlv01]

Maybe something like this.

Private Sub add_row_list_Click()
    With Selection
        With ActiveCell.Offset(1).Resize(1, 9)
            .Insert shift:=xlDown
            .Offset(-1, 0).FillDown
            .Resize(2).Insert shift:=xlDown
        End With
        ActiveCell.Offset(1, 0).Select
    End With
End Sub

 

[kamranyd]

How about this?

Private Sub add_row_list_Click()
    With Selection
        .Offset(1).Resize(1, 9).Insert shift:=xlDown
        .Offset(1, 1).Resize(, 8).FillDown
        .Offset(1, 0).Select
    End With
End Sub

thnx

 

[kamranyd]

Maybe something like this.

Private Sub add_row_list_Click()
    With Selection
        With ActiveCell.Offset(1).Resize(1, 9)
            .Insert shift:=xlDown
            .Offset(-1, 0).FillDown
            .Resize(2).Insert shift:=xlDown
        End With
        ActiveCell.Offset(1, 0).Select
    End With
End Sub

thanx i can use these codes somewhere else, but for now Mr. Cubist codes work for me.

 

[rlv01]

Glad you found something that worked.