Insert Row using Range objective

[Vandana Jain]

Greetings there,

I have been trying to write code for a problem.
Problem is:

I need to insert some rows at 4 places in a worksheet at a time but at different ranges in same number.
Like, I need to insert 4 rows at Range 12, 24, 30, 45.
Every time, I will add rows at 4 places for sure but ranges would be different.

I have written some code for this:



Dim J as Long, Rng as String, I as Integer, K as Integer
J = InputBox("Type the number of paths to be inserted")
For K = 1 To 4
Rng = InputBox("Enter your range")
For I = 1 To J
Range(Rng).EntireRow.insert
Next
Next




Now, this block of code shows some error with Range Objective.
Please help me out.

 

[MARK858]

Try...

Sub insertRws()
    Dim vSplit, strName As String, i As Long, j As Long
    
    j = InputBox("Type the number of paths to be inserted")

    strName = InputBox(Prompt:="Input values separated by comma", _
                       Title:="Insert row position", Default:="Input values separated by comma i.e. 5,9,12")

    vSplit = Split(strName, ",")
    For i = LBound(vSplit) To UBound(vSplit)

        Rows(vSplit(i)).Resize(j).Insert
    Next i

End Sub

 

[MARK858]

or

Sub insertRws()
    Dim vSplit, strName As String, i As Long, j As Long
    
    j = InputBox("Type the number of paths to be inserted")

    strName = InputBox(Prompt:="Input values separated by comma", _
                       Title:="Insert row position", Default:="Input values separated by comma i.e. 5,9,12")

    vSplit = Split(strName, ",")
    For i = j To LBound(vSplit) Step -1

        Rows(vSplit(i)).Resize(j).Insert
    Next i

End Sub

depending on exactly what your interpretation of where to input the rows is.
Row numbers must be entered in ascending order

 

[MARK858]

Going crazy on the last code try this one instead (and the one in post #2)

Sub insertRws()
    Dim vSplit, strName As String, i As Long, j As Long
    
    j = InputBox("Type the number of paths to be inserted")

    strName = InputBox(Prompt:="Input values separated by comma", _
                       Title:="Insert row position", Default:="Input values separated by comma i.e. 5,9,12")

    vSplit = Split(strName, ",")
    For i = UBound(vSplit) To LBound(vSplit) Step -1

        Rows(vSplit(i)).Resize(j).Insert
    Next i

End Sub

Row numbers still must be entered in ascending order

 

[Vandana Jain]

This one is working properly ??

 

[Vandana Jain]

Shows run time error 1004:
Application defined or object defined error

at line:
Rows(vSplit(i)).Resize(j).Insert


Help me out

 

[MARK858]

Put a single number in the first input box say a 4.
Put a series of numbers in ascending order separated by a comma in the 2nd inputbox i.e. 6,15,19.

and yes both the codes in post #2 and #4 work for me without error.

They do both give different results (and are supposed to).

You will get a 1004 error if the sheet is protected and you haven't allowed rows to be inserted.