add number of rows determined by combobox data

[redspanna]

Hi all

Userform asks how many blank rows to be inserted and a number is chosen
Once 'add' button is selected this number is placed into A2 of data worksheet

I want that number of rows added at the following point in the data sheet...

Column B
last populated cell
up 6 rows

so if the number entered into cell A2 (via the userform) is 9 I want 9 rows inserted from the last populated cell in coumn B offset up 6 rows, so if cell B90 was the last cell populated, insert 9 rows from row 84

hope this makes sense and many thanks

 

[Damon Ostrander]

Hi Redspanna,

Here is some code:

Sub AddRows()
   Dim nAdd    As Integer
   Dim iRow    As Integer
   Dim nRow    As Long
   
   nAdd = Range("A2").Value
   
   'get row after which rows will be inserted
   nRow = Range("B65536").End(xlUp).Row - 6
   
   'insert rows
   For iRow = 1 To nAdd
      Rows(nRow + 1).Insert
   Next iRow
End Sub

This gets the number of rows to add from cell A2, but if you wish to place the code in the Add button's Click event you could just as easily get the number of rows directly from the control on the userform where the user enters it.

My code also assumes the worksheet of interest is the currently active worksheet.

Keep Excelling.

Damon