Run-Time error and crash after deleting a row in a table in vba


New Member
Sep 27, 2017
MR. Excel Help

Hi all,

I am having trouble with a delete row macro for one of my tables in excel.

When I run my "Call_Delete_Row" code in an existing table after I open my excel spreadsheet, the last row for two tables on two different sheets are deleted.

However, AFTER I run "Insert_Rows" to add rows to two tables on two different spreadsheets tables, and I try to run "Call_Delete_Row" again, excel crashes. Furthermore, if I get an error, it says excel states their is a run-time error: '-2147417848 (80010108)' "Method 'Delete' of object 'ListRow' failed.

But, after excel crashes "Call_Delete_Row" runs.

Is their a solution to this problem?

The Code mentioned earlier is located below and references tables in sheets "BlndSht" and "MSN", and tables "Blend_Sheet" and "Material_Safety".
Sub Insert_Row()

Dim ws As Worksheet
Set ws = Worksheets("BlndSht")
Dim tbl As ListObject

Dim ws2 As Worksheet
Set ws2 = Worksheets("MSN")
Dim tbl2 As ListObject

Set tbl = ws.ListObjects("Blend_Sheet")

Set tbl2 = ws2.ListObjects("Material_Safety")

End Sub

Sub Delete_Row_BlndSht()

Dim ws1 As Worksheet
Dim tbl1 As ListObject
Dim LastRow, LastRow2 As Long

Set ws1 = Worksheets("BlndSht")
Set tbl1 = ws1.ListObjects("Blend_Sheet")

With tbl1

LastRow = tbl1.ListColumns(1).Range.Rows.Count

For i = tbl1.Range.Rows.Count To tbl1.Range(2, 1).Rows Step -1
If i = 2 Then Exit For

LastRow2 = tbl1.Range.Rows(LastRow).Delete

Next i

End With

End Sub

Sub Delete_Row_MSN()

Dim ws2 As Worksheet
Dim tbl2 As ListObject
Dim LastRow, LastRow2 As Integer
Dim i As Long

Set ws2 = Worksheets("MSN")
Set tbl2 = ws2.ListObjects("Material_Safety")

With tbl2

LastRow = tbl2.ListColumns(1).Range.Rows.Count

For i = tbl2.Range.Rows.Count To tbl2.Range(2, 1).Rows Step -1
If i = 2 Then Exit For

LastRow2 = tbl2.Range.Rows(LastRow).Delete

Next i

End With

End Sub
Sub Call_Delete_Row()

Call Delete_Row_MSN
Call Delete_Row_BlndSht

End Sub


Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics