Problem (error) deleting row from table using userform (VBA)

[Yede]

Hello,

I am using a userform to add, edit and delete data from a table located in the workbook. (code below is to delete)
With the addition of a new row into the table, I give the row a unique number. When the user in the form double clicks in the Listbox on the line they want to remove.
the data is being returned to the boxes inside the form(same as used to add data).

The removal of the row relies on a unique number and uses that to search the table for the row to delete.
that nr is then located in me.Nr

when I try to delete the data sometimes it works 2 times then it throws an error:
-2147417848 Meth "delete of object listrow failed

Then in the worksheet "PlanningLCMSworksheet" where the table is located, the data from the row is deleted however the row is still there and I cannot select cells on the sheet anymore.
Also when I try to save the file is crashes.

when I then try to use the form to delete more it throws up another error on line:

Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)

With the error:
application-defined or object-defined error


I cannot find the reason why.
Any help would be appreciated

Cheers,
Yede

Private Sub Deletebtn_Click()
'declare the variables
Dim datadelete As Range
Dim cDelete As VbMsgBoxResult
Dim DataSH As Worksheet
Dim tabelnaam As String
Dim tabelobject As ListObject
tabelnaam = "Plandata"
Set DataSH = Sheets("PlanningLCMSworksheet")
Set tabelobject = DataSH.ListObjects(tabelnaam)
'error statement
On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Nr.Value = "" Or dagweek.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete the data?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
'Delete value
tabelobject.ListRows(datadelete).delete
End If
Private Sub Deletebtn_Click()
'declare the variables
Dim datadelete As Range
Dim cDelete As VbMsgBoxResult
Dim DataSH As Worksheet
Dim tabelnaam As String
Dim tabelobject As ListObject
tabelnaam = "Plandata"
Set DataSH = Sheets("PlanningLCMSworksheet")
Set tabelobject = DataSH.ListObjects(tabelnaam)
'error statement
'On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Nr.Value = "" Or dagweek.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete the data?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set datadelete = DataSH.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
'Delete value
tabelobject.ListRows(datadelete).delete
End If
Application.ScreenUpdating = True
Unload Me
Toevoegen.Show
Exit Sub
errHandler:
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"
End Sub
Unload Me
Toevoegen.Show
Exit Sub
errHandler:
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"
End Sub

 

[MasterVBA]

Hello,

I offer you a simple solution to delete a line , if Your TAble is a Sheet on your workbook

Example on My Project :

If (UCase(Sheets("Feuil3").Cells(i, 3)) = UCase("OK")) Then ' SIf My COlomn contains "OK"
Rows(i).Clear ' I clear My Colomn
End If

On Error Resume Next
res = [A7:A55555].SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'I delete all the lines where Column A is empty

 

[Yede]

Hello,

Thanks for your response!
however, I need to only remove a row from a table that is located on a sheet.
I tried to edit the code you gave me to my own. But it removed an entire sheet!

If Sheets("PlanningLCMSworksheet").cells(i, 1) = datadelete Then 'SIf My Column contains the data that needs to be removed
Rows(datadelete).Delete
End If
Next
On Error Resume Next
res = [A1:A100000].SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'I delete all the lines where Column A is empty

Cheers,
Yede

 

[MasterVBA]

Hello

You can test the below code : Rows(i).clear instead of Rows(datadelete).Delete

If Sheets("PlanningLCMSworksheet").cells(i, 1) = datadelete Then 'SIf My Column contains the data that needs to be removed
Rows(i).clear
End If
Next
On Error Resume Next
res = [A1:A100000].SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'I delete all the lines where Column A is empty

 

[Yede]

Hello,

Thanks again for the help.
But does not work for me. It clears the cells but does not remove the row from the table.

I use the userform to add a row with the data that the user has put in the form.
Now with both the add and the delete row errors sometimes show up.

Method Delete (or add depends on adding of deleting row) of object listrow failed.

So the code I have written down works. but only a few times or sometimes errors on the first try.
Can it be something with the Table when removing a row in the middle?
I can't find anything on the internet also about the error.
Only with the .add row to add (position:=1) at the end. But I don't want the entry to be at the top I want it to remain on the bottom so the error still exists.

the error that is thrown up at delete code is here:
tabelobject.ListRows(i - 1).Delete

Error on add code is here:
Set newrow = ws.ListObjects("plandata").ListRows.Add

When the errors are thrown up the sheet just crashes and the whole file crashes.

cheers,
Yede

Delete code how I have it now:

Private Sub Deletebtn_Click()
Dim ws As Worksheet
Dim datadelete As Range
Dim cDelete As VbMsgBoxResult
Dim tabelnaam As String
Dim tabelobject As ListObject
Set ws = Sheets("PlanningLCMSworksheet")
tabelnaam = "Plandata"
Set tabelobject = ws.ListObjects(tabelnaam)
'error statement
'On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Nr.Value = "" Or dagweek.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete the data?", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set datadelete = ws.Range("Plandata[Nr]"). _
Find(What:=Me.Nr.Value, LookIn:=xlValues, LookAt:=xlWhole)
'Delete value
i = datadelete.Row
tabelobject.ListRows(i - 1).Delete
End If
Application.ScreenUpdating = True
Unload Me
Toevoegen.Show
Exit Sub
errHandler:
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"
End Sub

Add code how I have it now:

Private Sub Savebtn_Click()
Dim ws As Worksheet
Dim newrow As ListRow

Set ws = Sheets("PlanningLCMSworksheet")

Set newrow = ws.ListObjects("plandata").ListRows.Add

With newrow
newrow.Range(1) = Sheets("PlanningLCMSworksheet").Range("I1").Value + 1
newrow.Range(2) = Me.Analyse.Value
newrow.Range(3) = Me.dagweek.Value
'newrow.Range(4) = "=CONCAT([@Analyse];" - ";[@Dag]"
newrow.Range(5) = Me.Samplenr.Value
newrow.Range(6) = Me.Apparaat.Value
End With

Unload Me
Toevoegen.Show
End Sub