Share via

method add of object listrows failed

  • Article

Question

Tuesday, February 7, 2012 8:22 PM

I have an VBA application that I've built within an .xlsm file.   The following code is giving me an ongoing headache in that after it's been invoke a couple of time (or a dozen times--there's no predicting), I get an error message:  "method add of object listrows failed".  If I debug, the problem line is the ListRows.Add line.  If I continue executing the code, I get a 1004 error message related to the same line and then Excel freezes and crashes and then restarts.  I'm at a loss as to what the problem could be.  I have code to flush the clipboard and the FreeIt function you see below unprotects the sheet where the ListObject resides.  Also, I sometimes get the error when invoking another sub the deletes lines from the ListObject.  And when I've tried to workaround the Add method by simply resizing the ListObject.Range by a single row, sooner or later I get the same error message, only it's "method resize of object range failed". 

I've been up and down the forums out there and  can't find a satisfactory answer. 

Private Sub InsertRow()
    Dim RowNr As Integer
    Dim b As Boolean
        
    b = FreeIt()
 
    If line <> -1 Then
        RowNr = line
    Else
        RowNr = Target.Rows.Count
    End If

    ClearClipboard
    
    ThisWorkbook.Worksheets("Configurator").ListObjects("Products").ListRows.Add

    RevertIt (b)
End Sub

All replies (5)

Tuesday, February 7, 2012 9:30 PM ✅Answered

Try to declare and set the Listrow first, then to Add a New row, see below example:

Sub test()

   Dim wrksht As Worksheet
   Dim oListRow As ListRow
   
   Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
   Set oListRow = wrksht.ListObjects(1).ListRows.Add

   oListRow.Range.Cells(1, 1).Value = "Value For New cell"

End Sub

Hope this helps,

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Friday, January 11, 2013 3:53 PM

I have been experiencing the same problem with my code. Have not been able to find any solution online neither have i been able to find a workaround.

How ever I found a very interesting and extensive comment from whitew5720 on MrExcel.com.

Friday, January 18, 2013 11:18 AM | 2 votes

Hi,

In my case adding (Position:=1) in the end of ListRows.Add statement solved the problem.

.ListRows.Add (Position:=1)

But I am still not sure if it is the "right" solution. Following are some aspects of issue I was facing.

Context and Environment:
I have a fairly complex data-logging and maintaining Excel application. It is used by many vendors (about 12) operating in different environments. Each vendors creates its own copy of the application and then store it on team-site assigned to them. Most vendors excess the team-site via a terminal server. Each vendor might have different it-environment from which they log onto the terminal server.

File is in .xlsm format and code is only compatible with excel 2007 and onwards. But the terminal server supports both Excel 2003 and Excel 2010. But for some vendors, I don’t know why, cases terminal-server open the file in Excel 2003. But the issue didn’t seem to be linked, but now I am rethinking.

Issue:
Only some vendors experience the issue with ListRows.Add statement. And once the code fails file become unresponsive and no data is saved. Creating the new copy from the master would not seem to solve the problem. So the problem seemed vendors specific.

Concluding Remarks:
Now adding the (Position:=1) line at the end of ListRows.Add have solved the problem. When position option is left out, the default is that excel adds a row at the bottom of the table. To me this suggests that issue is related to Excel not being able to determine the position of the last row of the table. Which then might imply that table is somehow corrupted. This conclusion is not much different than one reached by whitew5720 on MrExcel.com in his very interesting and extensive comment on the same issue.

Regards

Friday, November 2, 2018 6:40 AM

I had this problem too, and it is very frustrating because the problem seemed intermittent and was not always a problem.

However I did find this:

If my Macro button which adds the row was initially on Sheet1 and that being the same sheet where the table object existed, then I had no problems despite having tried to crash it.

If my Macro button was moved to Sheet 2 and did not share the same sheet as the table object, then I would soon have a problem. within to or three executions of the macro I could repeat the error. but the funny thing was I had to got sheet 1, click any cell, go back to sheet 2, run macro, and then it crashes. or I might even have had to go select two different cells on sheet1 before the error happened.

So my work around is to run the Macro from the same sheet where the table is kept :|

Friday, November 2, 2018 6:48 AM

I went back and tried asifhayat2ibf's solution and that seemed to have solved the problem.

ListRows.Add (Position:=1)

Thanks asifhayat2ibf's