CODEWITHSUNDEEP
vbaexcelrows
user6888830
user6888830

Reputation: 75

Insert Specific Number of Rows

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

Upvotes: 6

Views: 44856

Answers (2)

pascal b
pascal b

Reputation: 371

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

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 13

Related Questions