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

Yede

New Member
Joined
Oct 14, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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




VBA Code:
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MasterVBA

New Member
Joined
Oct 14, 2020
Messages
4
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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

New Member
Joined
Oct 14, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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

New Member
Joined
Oct 14, 2020
Messages
4
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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

New Member
Joined
Oct 14, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

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
Top