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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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: