How to insert or delete partial row in Excel?
Inserting or deleting a complete row in Excel is a fundamental skill familiar to most users. However, there are many practical scenarios where you only need to add or remove a segment of a row (that is, several contiguous or non-contiguous cells in a row), without affecting the entire row's contents. For example, you may want to shift certain items to the right in one part of a data table, or remove data from only a few cells in a specific row while keeping the rest untouched. This need often arises when managing complex spreadsheets containing structured data, schedules, or forms, making precise adjustments especially valuable. If you're unsure how to insert or delete only part of a row instead of the whole row, this article will provide you with step-by-step guidance and practical solutions.
➤ Insert or delete partial row with VBA code
Insert or delete partial row with Insert/Delete Cells function
Suppose you need to insert or delete only part of a row—such as three specific cells—in row 5. The Insert and Delete Cells functions in Excel let you do this with just a few clicks. They’re ideal when you have a clearly defined set of cells to change within a row or a consistent region that needs periodic updates.
Insert partial row
1. Select the exact number of contiguous cells in row 5 where you want the new cells to appear. For example, if you want to insert three cells, select three adjacent cells in row 5. The number of selected cells must match the number you intend to insert to keep alignment.
2. Go to the Home tab, click Insert > Insert Cells. See screenshot:
3. In the Insert dialog box, choose: Shift cells right to push existing cells on the right further along the row; or Shift cells down to push cells downward (less common for horizontal insertions). Click OK.
Excel inserts the new cells and shifts existing content according to your choice. Example result:
- Formulas & references: Inserting with Shift cells right can move referenced cells; review dependent formulas afterward.
- Contiguous selection: Insert works best with contiguous cells; avoid mixed non-adjacent selections.
Delete partial row
1. Select the specific contiguous cells in row 5 that you want to remove (e.g., three adjacent cells).
2. Go to the Home tab, click Delete > Delete Cells.
3. In the Delete dialog box, choose: Shift cells left to pull cells from the right into the gap (typical for horizontal deletions); or Shift cells up to pull cells from below upward (used for vertical adjustments). Click OK.
The selected cells are deleted and remaining content shifts based on your selection.
- Check formulas: Deleting with Shift cells left can affect same-row references; verify results.
- Non-contiguous cells: Perform deletions on contiguous groups to avoid unexpected layout shifts.
- Undo: Use Ctrl+Z to revert immediately if the layout isn’t as intended.
These built-in options are straightforward for one-off or infrequent edits. For repetitive or large-scale operations, consider a VBA approach.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Insert or delete partial row with VBA code
For more advanced users or for repetitive tasks, VBA (Visual Basic for Applications) provides a flexible way to programmatically insert or delete segments within rows. This approach is highly effective when dealing with large datasets where manual operations would be too time-consuming, or when partial row operations must be customized beyond Excel's standard options.
You can use the following VBA solutions to insert or delete partial rows in Excel. These macros automate cell manipulation based on your parameters.
1. Select any cell in the row where you wish to insert partial rows. Then press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Copy and paste the VBA code shown below into the code window.
VBA code: Insert partial row in Excel
Sub InsertRow()
Dim I As Long
On Error Resume Next
LInput:
I = Application.InputBox("Enter number of rows required.", "Kutools for Excel", 1, , , , , 1)
If I < 1 Then
MsgBox "Error, please retype", vbInformation, "Kutools for Excel"
GoTo LInput
End If
Cells(ActiveCell.Row, 1).Resize(Int(I), 3).Insert xlDown
End SubParameter explanation: In this VBA code, 1 (the first parameter) specifies that the insertion begins with column 1 (i.e., column A), while 3 (the second parameter) determines that three new cells will be inserted across the selected row. You can modify these numbers to match your exact needs, such as inserting cells starting from a different column or changing the number of inserted cells. This offers flexibility for customized data structures.
3. After pasting the code, press F5 or click the Run button to execute the macro. A Kutools for Excel dialog box will appear asking you for the number of rows you wish to insert. Enter your desired number (for example, 3) and click OK.
After confirming, the macro will insert the specified number of partial rows with your chosen number of columns starting at the designated location in your worksheet. This method avoids manual repetitive actions and ensures accuracy in complex tasks.
If you instead need to delete several partial rows, you can use the following VBA code. This is especially useful for cleaning up structured data or removing outdated segments within a dataset efficiently.
VBA code: Delete partial row in Excel
Sub DelActiveCell_Row()
Dim I As Long
On Error Resume Next
LInput:
I = Application.InputBox("Enter number of rows required.", "Kutools for Excel", 1, , , , , 1)
If I < 1 Then
MsgBox " Error, please retype", vbInformation, "Kutools for Excel"
GoTo LInput
End If
Cells(ActiveCell.Row, 1).Resize(Int(I), 3).Delete xlShiftUp
End SubAfter running this code, a Kutools for Excel dialog box will prompt you to enter the number of partial rows you wish to delete. Input the appropriate value and click OK. The code will immediately remove the specified number of partial rows from your worksheet.
- Save first: Macros can’t be undone with Undo. Save a backup before running.
- Macro settings: Ensure macros are enabled and the sheet/workbook isn’t protected.
- Contiguous ranges: These procedures act on contiguous blocks starting at the active row, column 1, over 3 columns, and spanning I rows.
- Formulas & references: Inserting/deleting cells can shift references; review any dependent formulas after running.
In summary, the method you choose depends on your data layout, your need for automation, and Excel's built-in constraints. For routine modifications, Insert/Delete Cells covers most needs; for automation or more complex scenarios, VBA enables greater flexibility. Always verify your data after operations to prevent unexpected changes, especially when shifting cells, as references and formulas may update automatically.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in