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

[stroffso]

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

 

[My Aswer Is This]

Try this:

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]

That is just perfect, thanks so much

 

[My Aswer Is This]

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

That is just perfect, thanks so much

 

[stroffso]

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]

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.

 

[stroffso]

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.

No problem thanks