Insert n rows in a table

Excel Excel 2007
February 16, 2010 at 21:01:39
Specs: Windows XP
I want to add "n" rows to a table at a specific position, if "n" value is entered in R2C5 (a cell)
Kindly help if its possible in Macro

edited by moderator: Post moved from Windows 7 Forum

See More: Insert n rows in a table

Report •

February 17, 2010 at 04:16:44
You didn't say where you wanted the rows inserted, so I chose below Row 14 as an example.

Right-Click the Sheet tab for the sheet you want this to run in.
Chose View Code and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
'Disable events so code doesn't fire after insert
 Application.EnableEvents = False
'Was change made to R2C5?
  If Target.Address = "$E$2" Then
'Was a number entered?
   If IsNumeric(Target) Then
'Insert N Rows below Row 14
    Rows("15:" & 14 + Target).Insert Shift:=xlDown
   End If
  End If
 Application.EnableEvents = True
End Sub

Report •

February 17, 2010 at 05:20:02
Thanks a lot for this.
Also if you could advice... when i insert a line say at row 14 (assuming row 13 is the table title); it takes the table title properties (Bold/Coloured)
Is there a way to retain properties of the contents of the table and not the title?

Report •

February 17, 2010 at 06:46:35
I can think of 3 options:

1 - Insert a new Row 14. Format the new Row 14 to be the same as your table, then hide the row. When the insert occurs, it will pick up the formatting of Row 14, not 13.

2 - Another option is to do a "Copy - Insert Copied Cells" and then clear the contents. This should retain the formatting of the Copied cells.

3 - Finally, we could do the formatting within the macro by picking up the format(s) of the existing table and reformatting the new rows after the insert.

Which option do you think would work best for you?

Report •

Related Solutions

February 17, 2010 at 07:06:35

I have a slightly different solution.

As you referred to a Table (called Lists in earlier versions), this macro adds a row or rows wherever you have selected a cell inside a table.

Select a cell inside a table, then run the macro, it asks for the number of rows to add, then adds a row of cells only in the table - it doesn't add whole rows across the worksheet.

It copies the selected row's format to the new row or rows.

This macro works for any number of tables in a worksheet - it is not specific to one table.

You can link the macro to a button on the spreadsheet or run it from Alt+f8

The code goes into the 'ThisWorkbook' object.
Use Alt+f11 to go to the VB window, and select the ThisWorkbook object under your filename in the Project Explorer window on the left. Double click ThisWorkbook and paste the code into the VB window on the right.

Sub ListInsert()
Dim intCols As Integer
Dim strRows As String
Dim objList As ListObject
Dim rngListRow As Range
Dim n As Integer

On Error GoTo ErrHnd

'test if in a list
On Error Resume Next
Set objList = ActiveSheet.ListObjects(ActiveCell.ListObject.Name)
If Not objList Is Nothing Then
On Error GoTo ErrHnd
'create a range 1 row deep and the width of the List
intCols = objList.ListColumns.Count
Set rngListRow = ActiveSheet.Cells(ActiveCell.Row, objList.ListColumns(1).Range.Column)
Set rngListRow = rngListRow.Resize(1, intCols)
'insert cells, moving remining cells down
'get number of rows
strRows = InputBox("Enter number of rows to insert" & vbCrLf _
& "or 'Q' to quit", objList.Name)
'test for Q
If strRows = "Q" Or strRows = "q" Then
Exit Sub
'test that response is a number
If Not IsNumeric(strRows) Then GoTo InpBox
'insert required number of rows
For n = 1 To CInt(strRows)
rngListRow.Insert Shift:=xlShiftDown
'copy formatting from selected row & paste into new row
rngListRow.Offset(0, 0).Copy
rngListRow.Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
Next n
'remove copy marquee
Application.CutCopyMode = False
'select single cell
End If
On Error GoTo ErrHnd
'not in List or Table
MsgBox "The selected cell must be in a Table or List"
End If
Exit Sub

'error handler
End Sub


Report •

March 5, 2010 at 06:54:23
Dear Humar,

I just tried the above piece of code and for me it 'partly' works!

But what I would need:

Copy the content of the current row n-times below the current row. How can I do that?

Thanks a lot in advance! :)

Report •

Ask Question