VBA to Insert Table Rows with Input

[SanjayGMusafir]

I have a worksheet with many tables in it. In that sheet there is a table "EBank2".

I need to add multiple rows in this table based on the value in an input box.

Further, If it may help me add rows below a specific active cell, would be great.

I have tried many codes but nothing has worked for me.

Please help.

Thanks a lot

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

 

[SanjayGMusafir]

@mrshl9898 Thanks for taking efforts.

I tried both options. Neither worked. It gives error.

Here's a plan B

Sub InsertRows()

Dim myrow As Integer
Dim x As Integer


myrow = ActiveCell.Row


    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)


Rows(myrow).EntireRow.Resize(x - 1).Insert Shift:=xlDown
    
End Sub

 

[SanjayGMusafir]

@My Aswer Is This Thanks for reverting. I just tried this code. The system somewhat hanged after giving the input figure. And rows didn't add.

Try this:

Sub ResizeTable()
'Modified  9/10/2018  5:11:22 PM  EDT
Dim ans As Long
Dim rng As Range
Dim tbl As ListObject
ans = InputBox("Add How many rows?")
  
    Set tbl = ActiveSheet.ListObjects("EBank2")
    Set rng = Range("EBank2[#All]").Resize(tbl.Range.Rows.Count + ans, tbl.Range.Columns.Count)
    tbl.Resize rng
End Sub

 

[My Aswer Is This]

Are you sure your table is named:

EBank2

And this table is on the same sheet your running this macro on​

 

[SanjayGMusafir]

@My Aswer Is This I just realized that it added the rows at the bottom of the table. But the pointer from Excel just goes away, so have to reopen file again.

Further, I possible I wanted to insert rows beneath some active cell in the table.

Please help.

Thanks

Try this:

Sub ResizeTable()
'Modified  9/10/2018  5:11:22 PM  EDT
Dim ans As Long
Dim rng As Range
Dim tbl As ListObject
ans = InputBox("Add How many rows?")
  
    Set tbl = ActiveSheet.ListObjects("EBank2")
    Set rng = Range("EBank2[#All]").Resize(tbl.Range.Rows.Count + ans, tbl.Range.Columns.Count)
    tbl.Resize rng
End Sub

 

[SanjayGMusafir]

@My Aswer Is This Definitely Yes.

I just realized that it added the rows at the bottom of the table. But the pointer from Excel just goes away, so have to reopen file again.

Further, I possible I wanted to insert rows beneath some active cell in the table.

Please help.

Thanks

Are you sure your table is named:

EBank2

And this table is on the same sheet your running this macro on​

 

[My Aswer Is This]

What does this mean?

But the pointer from Excel just goes away, so have to reopen file again.

What is a pointer?

How are you activating this script?

Are you clicking on a button?

Yes I know you said active cell if possible.

I have not determined if I can get that.
I think we should do things one at a time.
Now explain why you have to close file and reopen.


​

 

[SanjayGMusafir]

@My Aswer Is This

What does this mean?

But the pointer from Excel just goes away, so have to reopen file again.

What is a pointer? - The Mouse Pointer

How are you activating this script? - I have made a button, asigned macro to it. And click on it whenever required.

Are you clicking on a button? - I generally select 4th row in table (just a habit), and click on the Macro assigned Button

Yes I know you said active cell if possible. - Thanks

I have not determined if I can get that.
I think we should do things one at a time.
Now explain why you have to close file and reopen.


​

 

[My Aswer Is This]

Well if the script I provided requires you to close the Workbook and reopen each time then that means just this part of the script is not working for you.
So no need in me trying to do this after active cell.

 

[SanjayGMusafir]

@My Aswer Is This @mrshl9898 I just wrote a script myself. It's a novice work. It adds the number of rows I tell but only alternatively.

Can you help me improve this?

Thanks

Sub AddRows()
'
' AddRows Macro
'


'
    Dim x As Integer
    Dim i As Integer
    
    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
  
    i = x
    
    Do Until i = 0
    
    Selection.ListObject.ListRows.Add (i)
    
    i = (i - 1)
    
    If i = 0 Then
    Exit Sub
    End If
    
    Loop
    
    End Sub

 

[SanjayGMusafir]

@My Aswer Is This @mrshl9898

I just edited my script. Now it adds given number of rows at the top of the table.

Thanks. I got motivation and ideas from Master like you only. Still if you can help me improve this script would be great.

Sub AddRows()
'
' AddRows Macro
'


'
    Dim x As Integer
    Dim i As Integer
    
    x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
  
    i = x
    
    Do Until i = 0
    
    Selection.ListObject.ListRows.Add (x - i + 1)
    
    i = (i - 1)
    
    If i = 0 Then
    Exit Sub
    End If
    
    Loop
    
    End Sub