VBA runtime crash - Listrows.add


  • Has anyone ever run into this problem before?

    I have an extensive .xlsm which uses the simple command line :
    And 2 out of 3 times, it will cause a run-time error which will then crash Excel and restart it.

    The full piece of code is as follow (however even the simple example above bugs):

    Dim iTable As ListObject
        Dim newRow As ListRow
        sheetsArray = getSheetsArray

    For Each iSheetName In sheetsArray
            Set iTable = ThisWorkbook.Worksheets(iSheetName).ListObjects(1)
            Set newRow = iTable.ListRows.Add(Position:=iTable.ListRows.Count + 1)
            transferDataToRow newRow, iTable
    Next iSheetName[/CODE]

    Has anyone ever had this bug, which seems to be more of an Excel instability then an error with the code?

    Any solutions? The fact that I cant add listrows to listobjects seems pretty disastrous!
    Tuesday, August 27, 2013 7:47 PM

All replies

  • Re:  "Set newRow = iTable.ListRows.Add(Position:=iTable.ListRows.Count + 1)"

    According to the help file...
      "Position:   Optional Integer. Specifies the relative position of the new row."
      "If Position is not specified, a new bottom row is added."

    Worth a try...
    Set newRow = iTable.ListRows.Add


    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:54 PM
    Wednesday, August 28, 2013 1:46 AM
  • Originally, I was using that exact line :

    Set newRow = iTable.ListRows.Add

    It generated the runtime crash, so I found that using a parameter fixed it... for 2days.

    I don't know what to do anymore. I stumbled upon what seems to be an instability in Excel itself (as suggested in some other posts from January 2013 by different people).

    Wednesday, August 28, 2013 3:18 AM
  • The only other thing I can think of deals with the workbook size...

    Do the tables encompass entire columns?
    (you can't add a row when the bag is full)

    Too many sheets possibly?
    (Several hundred could be a problem)

    Workbook size in MB?
    (better keep in below 50 MB - below 10 is even better)

    Another thought...
      locked/protected rows on some of the sheets?
      errors in formulas on a sheet?
    Jim Cone

    • Edited by James Cone Wednesday, August 28, 2013 3:41 AM typo
    Wednesday, August 28, 2013 3:40 AM
  • Yeah, it's the locked/protected sheet that started it all and my entire Excel has been unstable ever since.

    But it really seems like something with Excel, as I should just get a regular error in VBA, not a crash.

    Because otherwise my file is only 115kb (it's an empty form that transfer all data to an Excel workbook used only for storing data)

    Wednesday, August 28, 2013 5:15 AM

  • Re:  "my entire Excel has been unstable ever since"

    A couple of things to try, other than reinstalling Excel...

    Copy all of the code in the module out to Notepad
    Delete the module (right-click it)
    Insert a new module and name it
    Copy the code from the Notepad file and paste into the new module.
    Compile, save and test.


    When opening the file, do an 'Open and Repair'.
    In the File | Open dialog the 'Open' button has a small arrow on it.
    Clicking the arrow provides the repair option.
    Jim Cone
    Wednesday, August 28, 2013 1:14 PM