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

ZStaebler

New Member
Joined
Sep 27, 2017
Messages
1
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")
tbl.ListRows.Add

Set tbl2 = ws2.ListObjects("Material_Safety")
tbl2.ListRows.Add

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
_____________________________________________

Thanks.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top