VBA Insert row causes Excel to crash


New Member
Mar 24, 2018
Excel crashes when inserting (and occasionally when deleting) rows in VBA. I have tried the following fixes, but none helped:

  1. Ran the Microsoft Office Repair program for Office 2010
  2. Deactivated all ADD-IN modules
  3. Tried: Application.EnableEvents = False

I've used various methods to insert rows. All crash Excel:

  1. ActiveCell.EntireRow.Insert Shift:=xlShiftDown
  2. Dim SelectedRange As Range
    Set SelectedRange = Selection
  3. Dim SelectedRange As Range
    Set SelectedRange = Selection.EntireRow

The error message (when it appears; most times it doesn't) is an EXCEL.EXE Application Error:
"The instruction at __________referenced memory at ___________. The memory could not be read.

Here's the module in its entirety:
Option Explicit

Sub Insert_Row()
' Inserts a row above the selected row
' Copies the selected plant/size from the ComboBox linked cell to the new inserted row in column 3 (offset 2)
    On Error GoTo Error_handler:
    Dim Response As Integer
    Dim SelectedRange As Range
    Set SelectedRange = Selection.EntireRow           'Save entire row selection based on active cell
' Ensure plant has been selected from dropdown list
    If Range("G1").Value = "" Then          'Test LinkedCell to see if plant selection has been made
        MsgBox "Please select a plant from the dropdown list"
        Exit Sub
    End If
' Unprotect sheet & Hide screen updates
    Application.ScreenUpdating = False
    Application.EnableEvents = False

' Insert row above selection and paste contents of selected row to inserted row
'        SelectedRange.EntireRow.Insert                 'CREATES FATAL ERROR
'        ActiveCell.EntireRow.Insert                    'TRIED THIS --> NOPE: This crashed too
'           (FROM https://www.mrexcel.com/forum/excel-questions/77371-vba-insert-row-colum.html)
'        ActiveCell.EntireRow.Insert Shift:=xlShiftDown  'TRIED THIS --> NOPE: This crashed too
'           (FROM https://powerspreadsheets.com/excel-vba-insert-row/#Specify-the-Active-Cell-with-the-ApplicationActiveCell-Property)
' ONLY WAY TO INSERT A ROW WITHOUT CRASHING: Put this Msgbox code in front of the INSERT statement
    Response = MsgBox("Is Entire Row Selected?", vbOKCancel, "Delete Row")
    If Response = vbCancel Then End
    If Response = vbYes Then
    End If
        SelectedRange.Insert Shift:=xlShiftDown
        SelectedRange.RowHeight = 14.25         'Reset row height (narrow row after last might be selected)
        SelectedRange.ClearContents             'Clear contents of inserted row

' Copy selected plant from dropdown list and paste into plant name column of inserted row
        Range(Cells(Selection.Row, 3).Address).PasteSpecial xlPasteAll 'Paste plant name into Column 3

' Copy TOT COST formula from row below and paste into TOT COST column of inserted row
        Range(Cells(Selection.Row + 1, 5).Address).Copy                'Copy TOT COST formula from row below
        Range(Cells(Selection.Row, 5).Address).PasteSpecial xlPasteAll 'Paste TOT COST formula into Column 5

GoTo Endinsert

MsgBox Err.Description
' Protect sheet & Show screen updates
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    End Sub


Active Member
May 16, 2017
May not be an Excel error at all. Have you tried running system file checker?

sfc /scannow

(you'll need admin privileges).


Active Member
May 16, 2017
Those kind of errors blow chunks because it's probably not anything you are doing coding-wise. I've had them myself and they can be terribly frustrating. Here are three things from good to 'only-if-you-are-at-your-wits-end' that have fixed this class of error for me in the past.

1. If you are still using a 32-bit version of Excel (does not matter if OS is 64bit) download Rob Bovey's excellent (and free) code cleaner and clean your VBAProject. This has cured a number of ills for me.


2. Abandon the workbook. Manually create a new one, copy and pasting data (and consider pasting values only, and then formats only) and code to the new WB. It's wierd but I've had un-explainable automation errors go away after doing this.

3. Completely uninstall office. Then do a clean reinstall. (Note that this is more that just running the repair option).
Last edited:


New Member
Mar 24, 2018
Thank you!
I created a "Test" version of the Workbook using an exact copy of the VBA Project. It works perfectly (after making a few minor changes).
I will get back into this tomorrow. It's very helpful to have someone to share with just so I know it's not just me (it never is).
Thanks again.


New Member
Mar 24, 2018
Hallelujah! I recreated the Workbook from scratch, copying sections from the old Workbook and Modules. Everything works perfectly.
All functionality added back (conditional formatting, custom views, etc) with no adverse effect.
The problem must have been something wrong with the original Workbook, which was originally created by someone else before I revised it.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...