Insert rows based on user input VBA Macro

[turk1077]

Hello all power users!

I am trying to write a little VBA the prompts the user for the number of rows to insert then inserts that number of rows at active cell. Here is the code I've got, but it doesn't work. I'm just getting started with VBA and I think there must be something fundamental that I am missing...

Sub InsertRows()

Dim numrows As Integer
numrows = InputBox("Number of Rows")
    ActiveCell.Rows("1:'numrows'").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Select
End Sub

 

[Smitty]

How about:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> InsertRows()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
        x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
        Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[turk1077]

That works perfectly. I guess I was going about it the wrong way.

-Turk

 

[heatherubs]

I found this today when looking for a way to insert a number of rows (that always varies for me). When I created the macro, I tested it with numbers smaller than 10 and it did work. However, when I try to use it for entering numbers larger than that, it isn't working so well. When I want 50, I might get 4 with this macro. The most I've needed has been 300, and it won't work for that. Is there a better macro to try? And if I'm being extremely picky, I would like it to insert the lines below my selected row instead of above. Would appreciate any help!

 

[SanjayGMusafir]

@Smitty

I just came to the string searching for a solution.

My problem is that when I use your code, it safely gives me the input box, gives error while inserting table rows.

Just to mention, my worksheet has many tables and I want to add rows to a specific table only, below an active cell.

Please help. Thanks a lot &#55357;&#56911;

How about:

Sub InsertRows()
Dim x As Integer
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown
End Sub


Hope that helps,

Smitty

 

[My Aswer Is This]

We need to know the Table name.
Please explain to me in detail what your wanting.
Do not ask me to read previously posted questions.

 

[SanjayGMusafir]

Thanks for taking innitiative @My Answer is This

I try to explain my requirement -

I have a table "EBank2". I need to add rows in this table based on the value I give in an input box.

If you may Please help. Thanks a lot &#55357;&#56911;

 

[My Aswer Is This]

I need a example:

What do you plan to put in the Inputbox?

2

And we will add two empty rows to Table

Or something else