Insert Specific Number of Rows

Question

I'm trying to insert a specific number of rows. Right now I'm using the below code to insert 4 lines. I'm trying to write a line of code that will insert a certain number or rows based on a number in a certain cell. For example, if I wanted to insert 4 rows and cell A2 is the cell where I can change the number of rows I want to add, what code would I use to add any number of rows based off what number I insert in cell A2.

ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

Solution 1

You can use something like the line below to insert 4 rows at once:

ActiveCell.EntireRow.Resize(4).Insert Shift:=xlDown 

or maybe the line below (depends on where you want the added rows to be added):

ActiveCell.EntireRow.Offset(1).Resize(4).Insert Shift:=xlDown

and without the need to use ActiveCell , which is always recommended:

Range("A2").EntireRow.Offset(1).Resize(4).Insert Shift:=xlDown

Solution 2

This should work

Sub InsertRow()

  Dim ws As Worksheet
  Dim NBOFROWS As Range
  Set ws = ThisWorkbook.ActiveSheet

  With ws
    Set NBOFROWS = .Range("A2")
    ActiveCell.EntireRow.Offset(1).Resize(NBOFROWS.Value).Insert Shift:=xlDown
  End With

End Sub