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

 

[mrshl9898]

Your code works fine for me, what issue are you having exactly? Do you have a cell on the EBank2 sheet activated when trying to insert?

 

[SanjayGMusafir]

Thanks for giving your time @mrsh9898 . I exactly dont know where the problem lies. But when I execute this vba the Debugger shows error in red highlighted line. This could be probably that my worksheet has many tables. As of active cells in table, I tried it that way only.

Please help

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

Your code works fine for me, what issue are you having exactly? Do you have a cell on the EBank2 sheet activated when trying to insert?

 

[mrshl9898]

What happens when you select the cell and add a row manually? Will it allow it?

 

[SanjayGMusafir]

Yes. It allows that way. I just thought to make my daily exercise by working with macro...

What happens when you select the cell and add a row manually? Will it allow it?

 

[SanjayGMusafir]

Yes. It allows that way. I just thought to make my daily exercise by working with macro...

I started with recording a macro and thought will improvise it by adding an input box. But the code I got, as a novice, didn't find a way out.

Though I have a layman's idea that if nothing else work, we can add a loop to add number of rows to the basic code until the rows to be added become zero. I t will work slow. Moreover I have idea and not knowledge to write such code.

Please help.

Thanks

What happens when you select the cell and add a row manually? Will it allow it?

 

[SanjayGMusafir]

@mrshl9898 The recorded macro looks like this.

Sub AddRows()
'
' AddRows Macro
'


'
    Selection.ListObject.ListRows.Add (4)
    Selection.ListObject.ListRows.Add (4)
    Selection.ListObject.ListRows.Add (4)
    Selection.ListObject.ListRows.Add (4)
End Sub

Yes. It allows that way. I just thought to make my daily exercise by working with macro...

I started with recording a macro and thought will improvise it by adding an input box. But the code I got, as a novice, didn't find a way out.

Though I have a layman's idea that if nothing else work, we can add a loop to add number of rows to the basic code until the rows to be added become zero. I t will work slow. Moreover I have idea and not knowledge to write such code.

Please help.

Thanks

 

[mrshl9898]

If it allows it manually there's nothing wrong with the code that would be stopping it happening, I understand you are wanting a macro to do it, this was just a check to look for what could be causing the error.

Try this, would be good to know what the range is.

Sub InsertRows()

Dim myrange As Range
Dim x As Integer


Set myrange = ActiveCell


Debug.Print myrange.ADDRESS


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

 

[mrshl9898]

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

 

[My Aswer Is This]

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