To insert a worksheet row with Excel VBA, use Rows(ActiveCell.Row).Insert Shift:=xlDown in a macro; it inserts a row above the active cell. If you do not need code, select a row and use Home > Insert > Insert Sheet Rows, or press the row-insert shortcut.
What Excel row-insertion code does
Inserting a row shifts existing worksheet cells down to make room. The new row takes the position you specify, while data below it moves to the next row. For example, inserting at row 5 places a blank row above the old row 5; it does not overwrite that row. Excel adjusts many formulas and references as part of the change, but you should still check formulas, charts, and other dependent features after changing a complex sheet.
VBA is useful when row insertion is part of a repeatable task: adding an entry area to a template, preparing a report, or inserting space at a predictable location. A macro is usually unnecessary for a one-off edit. The ribbon, right-click menu, or keyboard shortcut is quicker when you only need to add a row manually.
A VBA macro to insert a row above the active cell
This macro inserts a complete worksheet row above the row containing the active cell. It uses a worksheet variable so the target sheet is explicit, and a row-number variable so the insertion point is easy to identify.
Sub InsertRowAboveActiveCell()
' Insert a worksheet row above the active cell.
Dim ws As Worksheet
Dim insertAt As Long
Set ws = ActiveSheet
insertAt = ActiveCell.Row
ws.Rows(insertAt).Insert Shift:=xlDown
End Sub
If the active cell is in row 8, for example, the macro inserts a blank row 8 and moves the former row 8 and the rows beneath it down by one. The active cell is the current cell in the selection, so check that it is on the intended worksheet and row before running the macro. The code inserts a whole worksheet row, not just a cell within a selected table or range.
To always insert above row 5 instead, replace the line that sets insertAt with insertAt = 5. The rest of the macro can stay the same. The row number must be a valid row on the target worksheet.
How to add and run the VBA code
- Save a copy of the workbook before using a macro. VBA changes cannot be reversed with Ctrl+Z.
- Open the Visual Basic Editor. In Excel for Windows, press Alt+F11. On Mac, use Tools > Macro > Visual Basic Editor, or the keyboard shortcut available for your keyboard and Excel setup.
- In the editor, choose Insert > Module. Paste the macro into the new module window.
- Return to Excel, click a cell in the row where the new row should go, and run the macro. You can run it from Developer > Macros by selecting its name and choosing Run. If the Developer tab is not visible, you can enable it in Excel Options on Windows or Excel Preferences on Mac.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving it as .xlsx removes the VBA project. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Use the desktop app to run this code.
Insert a row without writing code
For a manual insertion in Excel for Microsoft 365 on Windows, select the row by clicking its number at the left edge of the worksheet. Then choose Home > Insert > Insert Sheet Rows. Excel inserts a row above the selected row. You can also right-click the selected row number and choose Insert.
The Windows keyboard shortcut is Ctrl+Shift++ (the plus sign; on many keyboards, press Ctrl+Shift+=). With a full row selected, this inserts a worksheet row. If only a cell is selected, Excel may show the Insert dialog, where you can choose Entire row. On Mac, Control+Shift+= is the corresponding common shortcut; if your keyboard layout handles the plus sign differently, use the ribbon or right-click menu instead.
In Excel for the web, use the row header context menu or the Home tab’s Insert options. Ribbon labels and available shortcuts can vary by platform, but selecting a row header first makes it clear that you want a whole worksheet row rather than a few cells.
Insert more than one row
To insert several adjacent rows manually, select the same number of existing row headers as the number of rows you want to add, then choose Insert Sheet Rows. For example, selecting row headers 6 through 8 and inserting adds three rows above the former row 6. The selected rows indicate the number of new rows; Excel does not replace the selected data.
For repeated work, you can adapt the macro to insert a specified count. This version inserts three rows above the active cell’s row:
Sub InsertThreeRowsAboveActiveCell()
' Insert three worksheet rows above the active cell.
Dim ws As Worksheet
Dim insertAt As Long
Set ws = ActiveSheet
insertAt = ActiveCell.Row
ws.Rows(insertAt).Resize(3).Insert Shift:=xlDown
End Sub
The Resize(3) expression makes the target range three rows tall before insertion. Change 3 to another positive number to set a different count. Be cautious with a macro that inserts rows repeatedly: running it twice makes two insertions, not one larger insertion that replaces the first.
Formatting, formulas, and worksheet tables
A new row is not always visually or functionally identical to the rows around it. Excel may carry formatting into inserted rows from adjacent cells. After inserting, check number formats, borders, data validation, conditional formatting, and formulas. If the row should match a particular neighboring row, use the Insert dialog’s formatting options or copy the intended formatting afterward. A macro can also specify a format origin, but the best source depends on the sheet’s layout.
Excel generally updates relative references when rows are inserted within a referenced range, but formulas can behave differently depending on where the insertion occurs and how the reference was constructed. Structured references in tables, named ranges, charts, and external links also deserve a quick review. Inserting a blank row does not automatically mean every related calculation or report includes the new row as intended.
Excel tables have their own row behavior. To add a row within a table, right-click a table cell and choose Insert > Table Rows Above. You can also move to the last cell of the table and press Tab to create a new table row. A table row is part of the table and can inherit its formatting and calculated-column formulas; a worksheet row inserted outside the table may not become part of it. If you need to expand a table by a specific area, use Table Design > Resize Table and verify the selected range.
Choosing between VBA, the ribbon, and a shortcut
- Use the ribbon or context menu for a one-time insertion, especially when you want to see the selected row before changing the sheet.
- Use the keyboard shortcut when you are working quickly and already have the correct row selected.
- Use VBA when insertion is one step in a repeated process or needs to happen at a specific row based on a larger macro.
- Use table-specific commands when the new record belongs inside an Excel table and should inherit table behavior.
Code is not automatically safer or more precise than a menu command. A macro using ActiveSheet acts on whichever sheet is active when it runs. For a workbook used by other people, a more controlled macro can refer to a named worksheet rather than the active one. That reduces the risk of inserting a row on the wrong sheet, although the worksheet name must match the workbook.
Common problems and how to avoid them
If a macro reports that it cannot insert rows, the sheet may be protected, or inserting would push non-empty cells past the worksheet’s last row. Check the worksheet protection settings and whether data or formatting extends unusually far down the sheet. A shared or restricted workbook may also limit structural changes.
If the macro inserts on the wrong sheet, confirm which worksheet is active before running it. This is a common consequence of using ActiveSheet, especially when several workbook windows are open. If the wrong row moves, use Undo only if Excel still offers it; a VBA macro commonly clears the normal undo history, so do not rely on Ctrl+Z. Restore the saved copy if necessary.
If the new row looks different, inspect nearby formatting and table boundaries rather than assuming the insertion failed. If a formula result is missing, check whether the formula was part of a table calculated column or whether the new row falls inside the range used by the calculation. A row can be inserted successfully even when the sheet’s formulas or presentation need follow-up.
Frequently Asked Questions
What is the VBA code to insert a row above the current row?
Use Rows(ActiveCell.Row).Insert Shift:=xlDown in a macro. Qualifying it with a worksheet object, as in the example above, makes the target sheet explicit.
How do I insert a row at a specific row number?
Use the desired row number in the insert call. For example, Rows(5).Insert Shift:=xlDown inserts a worksheet row above the current row 5. In a macro that uses a worksheet variable, use ws.Rows(5).Insert Shift:=xlDown.
Can I insert a row with a formula in Excel?
No worksheet formula can insert a worksheet row. Formulas calculate values; changing worksheet structure requires a manual command or automation such as VBA in desktop Excel.
Can I run VBA to insert a row in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app to run the macro, or insert the row using the web app’s interface.
Why did my macro insert a row on the wrong worksheet?
A macro that refers to ActiveSheet works on whichever sheet is active at run time. Activate the intended sheet first, or change the code to refer explicitly to the worksheet you want.
Related
- How to Protect a Sheet in Excel and Set Permissions
- Excel Print Area: Set It, Adjust Pages, and Print Clearly
- Data Link Excel to Civil 3D: Steps and Limits
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Google Sheets Macros: How They Work and How to Use ThemYes. Google Sheets supports macros: you can record a sequence of spreadsheet actions and replay it, or customize…
- VBA MsgBox Scroll: Limits and Scrollable AlternativesNo: VBA’s built-in MsgBox has no setting or argument that adds a scrollbar. For long text, use a…
- Access VBA: How to Send EmailYes. Access VBA can send email either through DoCmd.SendObject, which uses the computer’s configured MAPI email client, or…
- Excel VBA Copy and Paste a Range to Another WorkbookTo copy a range from one open Excel workbook to another with VBA, use sourceRange.Copy Destination:=targetSheet.Range("B2"), replacing the…
- Free Windows Macro Recorder: Options and How They WorkYes. You can record repetitive Windows tasks for free with Microsoft Power Automate for desktop, or use free…
- Excel VBA Programming: What It Is and How to Use ItExcel VBA programming means using Visual Basic for Applications, Excel’s built-in programming language, to automate workbook tasks and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.