VBA to insert multiple rows beneath all rows I've selected

[mlarson]

Hi all, looking for a little help on a code below. Thanks in advance!

The code below allows me to insert X number of rows when I have selected a given row. So, if I select row 3 and ask it to insert 2 rows on a popup, it inserts those 2 rows.

What I would like it to do is insert X rows after every row I select simultaneously. So, if I hold CTRL and select rows 3, 7, and 12 I would want it to add X rows after each of those rows.

I hope that's a small edit here. Thanks for your help!

Sub InsertRows()
Dim numRows As Variant
Dim i As Long

numRows = InputBox("Insert Number of Rows", "Insert Rows")

If numRows = "" Then Exit Sub
If Not IsNumeric(numRows) Then Exit Sub
For i = Selection.Rows.Count To 1 Step -1
Selection.Rows(i).Resize(numRows).Insert
Next i
End Sub

 

[johnnyL]

Insert - Inserts row(s) before a line selected, not after.

 

[johnnyL]

With that being said, you could do something like:

Sub InsertRows()
'
    Dim rg      As Range
    Dim numRows As Variant
'
    numRows = InputBox("Insert Number of Rows", "Insert Rows")
'
    If Not IsNumeric(numRows) Then Exit Sub
'
    For Each rg In Selection.Areas
        rg.Rows.Resize(numRows).EntireRow.Insert
    Next rg
End Sub

 

[johnnyL]

If you really want to 'Insert' row(s) after the selected rows, you could do something like:

Sub InsertRowsAfter()
'
    Dim rg      As Range
    Dim numRows As Variant
'
    numRows = InputBox("Insert Number of Rows", "Insert Rows")
'
    If Not IsNumeric(numRows) Then Exit Sub
'
    For Each rg In Selection.Areas
        rg.Rows.Offset(1).Resize(numRows).EntireRow.Insert
    Next rg
End Sub

 

[mlarson]

With that being said, you could do something like:

Sub InsertRows()
'
    Dim rg      As Range
    Dim numRows As Variant
'
    numRows = InputBox("Insert Number of Rows", "Insert Rows")
'
    If Not IsNumeric(numRows) Then Exit Sub
'
    For Each rg In Selection.Areas
        rg.Rows.Resize(numRows).EntireRow.Insert
    Next rg
End Sub

This worked! Thank you!

 

[johnnyL]

Glad to help.

Just so you know, the answer you marked as the solution is the version of code that inserts rows before the rows that you select. Your post #1 asked about inserting the rows after the selected rows which is what post #3 does.

 

[mlarson]

Glad to help.

Just so you know, the answer you marked as the solution is the version of code that inserts rows before the rows that you select. Your post #1 asked about inserting the rows after the selected rows which is what post #3 does.

Yeah, I think I misspoke when I said that. You stated it more clearly and correctly, so I liked the code that I marked as the solution.

 

[johnnyL]

Ok, just checking. Thank you for the clarification.

Also, a correction of what I previously said, post #4 is the code that inserts rows after the selected rows.