add number of rows determined by combobox data

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,462
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Redspanna,

Here is some code:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top