Code to insert multiple rows at a particular point using input boxes

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125
Hi All,

I found the following code but want to modify it slightly but not sure where I am going wrong.

I basically just want the following to happen
1. Input box asking on what row should the insert start
2. How many rows to be inserted
Click ok

The code below has a bit about intervals, I ahve tried reworking it but keep coming up with errors, if anyone can help that would be great.





Sub inerrtrows()
Dim NumRowsToInsert, FirstRow As Long
Dim RowIncrement As Long
Dim ws As Excel.Worksheet
Dim LastRow As Long
Dim LastEvenlyDivisibleRow
Dim i As Long


Do
FirstRow = InputBox("Please indicate at which row you want to start.")
Loop Until IsNumeric(FirstRow) 'keeps on asking until they enter an actual number, otherwise the program will lose its mind when the user inputs a character


NumRowsToInsert = InputBox("How many rows would you like to insert between each row of data?") 'any number greater than 0
RowIncrement = InputBox("How many rows of data between line inserts?")
Set ws = ActiveSheet
With ws
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastEvenlyDivisibleRow = Int(LastRow / RowIncrement) * RowIncrement
If LastEvenlyDivisibleRow = 0 Then
Exit Sub
End If
Application.ScreenUpdating = False
For i = LastEvenlyDivisibleRow To FirstRow Step -RowIncrement
.Range(i & ":" & i + (NumRowsToInsert - 1)).Insert xlShiftDown
Next i
End With
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,257
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
Sub Insert_Rows()
'Modified 6/20/18 3:00 AM EDT
Application.ScreenUpdating = False
'Dim ans As String
'Dim anss As string
ans = InputBox("Insert row starting on row ??")
If ans = "" Then MsgBox "You entered no row to start on" & vbNewLine & " I will now stop this script": Exit Sub
anss = InputBox("Insert how many rows ??")
If anss = "" Then MsgBox "You entered no number of rows to insert" & vbNewLine & " I will now stop this script": Exit Sub
Rows(ans).Resize(anss).Insert
Application.ScreenUpdating = True
End Sub
 

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
125

ADVERTISEMENT

On from the above there is one more little step I need to do. As I have all the data in a table with a lot of conditional formatting I also need to copy the conditional formatting to the newly added rows. Is there some code that could do this? I thought about just dragging cells down but there is data above and it wouldnt work, thanks

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,257
Office Version
  1. 2013
Platform
  1. Windows
You may want to start a new question for this:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top