VBA Insert row causes Excel to crash

[BuddhaJoe]

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
   SelectedRange.EntireRow.Insert
3. Dim SelectedRange As Range
   Set SelectedRange = Selection.EntireRow
   SelectedRange.Insert

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
    ActiveSheet.Unprotect
    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("G1").Copy
        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


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


    End Sub

 

[rlv01]

May not be an Excel error at all. Have you tried running system file checker?

sfc /scannow

(you'll need admin privileges).

 

[BuddhaJoe]

SFC ran error-free. Good idea, but that didn't help.

 

[rlv01]

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.

http://www.appspro.com/Utilities/CodeCleaner.htm

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).

 

[BuddhaJoe]

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.

 

[BuddhaJoe]

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.

<THIS THREAD IS COMPLETE>

 

[rlv01]

Glad you got it sorted out.