How to use macro to insert row below table in Excel?
It is common for us to add blank rows at the bottom of a specified table in order to expand the table and add new content. This article is talking about using a macro to insert a new row below a specified table and keep the formula of the above cell in Excel.
Please do as follows to insert a blank row below a specified table with macro.
1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please click Insert > Module to open the Module window. Then copy and paste the below VBA code into the window.
VBA code: use macro to insert a blank row below a specified table
Sub AddRows() 'Updated by Extendoffice 2017/9/12 Dim xLastRow As Long Dim xRg As Range Dim xAddress As String On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Please select the table range you will insert blank row below:", "KuTools For Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub xLastRow = xRg.Row + xRg.Rows.Count xRg.Rows(xLastRow).FillDown xRg.Rows(xLastRow).SpecialCells(xlCellTypeConstants).ClearContents End Sub
3. Press the F5 key to run the code. And a Kutools for Excel dialog box pops up, please select the certain table range you will add a blank row below, and then click the OK button. See screenshot:
Then you can see a new blank row is added at the bottom of the specified table. And formula in above cell is also reserved in the newly inserted row as below see screenshot shown:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 months agoSlightly shorter version: Activesheet.ListObjects(1).ListRows.Add