Solved Insert X Rows at Row Y with Button

May 31, 2016 at 13:23:16
Specs: Windows 7
I would like to have a button on a spreadsheet that allows me to insert x rows with x being user specified below a certain row y which would also be user specified. What would the VBA code look like?

See More: Insert X Rows at Row Y with Button

Report •


#1
June 1, 2016 at 00:27:30
✔ Best Answer
You can do something like this, note that this code will need to be placed within the buttons module, or called by the buttons click events

Sub InsertXRows()
    
    Dim xRows As Long
    xRows = InputBox("How many rows shall we add?", "Add rows")
    
    If IsNumeric(xRows) Then
        
        For i = 1 To xRows
            ActiveCell.Offset(1).EntireRow.Insert
        Next i
    End If
    
End Sub

What this will do is

Capture the active cell
display an inputbox to ask you how many rows you want
then insert them under the active cell
so for example if you have row 4 selected, it will add the number of rows below 4


Report •

#2
June 1, 2016 at 10:58:36
Just picking a nit here.

This version might be a little more efficient because it inserts the rows as a block, instead of looping and inserting 1 row at a time.

For just a few rows, the time difference is probably meaningless, but just as an example, 1000 rows took 6 seconds to "loop-insert" while 10,000 rows was basically instantaneous using the "block-insert" method.

Sub InsertXRows()
    
    Dim xRows As Long
    xRows = InputBox("How many rows shall we add?", "Add rows")
    
    If IsNumeric(xRows) Then
        Range(ActiveCell.Offset(1).Address & ":" & _
              ActiveCell.Offset(xRows).Address).EntireRow.Insert
    End If
    
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
June 2, 2016 at 00:15:34
Very nice Derby, I actually didn't know you could do that!! thank you for the guidance.

Report •

Related Solutions

#4
June 2, 2016 at 08:12:57
Thank you both! This worked really well. I am only adding 5-10 rows per click so the calculation time is not a big issues. So my next step for the program after adding the new rows at row y is to:

1) Drag down cells for columns A-L from row y into the newly added rows, copy cells do not fill series so Column B stays constant. I am trying to keep the formulas that are in the cells but not the values. The formulas are vlookup formulas pulling data from a separate tab. Column C contains the unique value that is user input. Columns A, E, F, G, H, I, K calculate based off value in Column C. Columns L, and J are user entered commentary for the rows. Its a production schedule. Column B is a constant.

2) After dragging down the cells then I would like to delete the values in Columns C, L, and J. The format calculated columns will update automatically when Column C is changed.

3) The third step is to assign a lot number in Column J to each row which consists of DDMMYYAAABB incrementing BB five values up starting at 01, 05, 10, 15 etc. to keep unique values for each row. AAA is a three digit constant. This may be tricky and I may have to leave it as manual entry.

Let me know if any ideas on what type of code might work for this.


Report •


Ask Question