VBA Insert row causes Excel to crash

BuddhaJoe

New Member
Joined
Mar 24, 2018
Messages
7
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:
Code:
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

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

sfc /scannow

(you'll need admin privileges).
 

rlv01

Active Member
Joined
May 16, 2017
Messages
469
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).
 
Last edited:

BuddhaJoe

New Member
Joined
Mar 24, 2018
Messages
7
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

New Member
Joined
Mar 24, 2018
Messages
7
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>
 

Forum statistics

Threads
1,081,526
Messages
5,359,279
Members
400,523
Latest member
ExcelNewbie98

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