Insert rows based on user input VBA Macro

turk1077 · Jan 27, 2005

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...

Code:

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 · Jan 27, 2005

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

turk1077 · Jan 27, 2005

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

-Turk

heatherubs · Sep 19, 2017

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 · Sep 9, 2018

@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;

Smitty said:
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 · Sep 9, 2018

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 · Sep 9, 2018

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 · Sep 9, 2018

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

Insert rows based on user input VBA Macro

turk1077

New Member

Excel Facts

Smitty

Legend

turk1077

New Member

heatherubs

New Member

SanjayGMusafir

Well-known Member

My Aswer Is This

Well-known Member

SanjayGMusafir

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Insert rows based on user input VBA Macro

New Member

Excel Facts

Legend

New Member

New Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock