Error 1004 - Delete method of range class failed

[Rfriend]

I have code I copied from a tutorial that is no longer available. When I use the code I get the 1004 error "Delete Method of Range Class Failed. I have nailed it down to th this line: findvalue.EntireRow.Delete, but I can't figure out why or how to get passed it. My worksheet is an employee roster. THe search is looking for a record number returned from a search to a list box in a user form. I double click the record and it fills 30 controls in the user form. I should then be able to delete the record selected based on the record number in control "Emp1". The code requires that the position number "Emp4" and record number "Emp1" be present to proceed.

The code is posted below. Any education and resolution is very welcomed at this point.

Thank you

Private Sub cmdDeleteEmp_Click()
'declare the variables
    Dim findvalue As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
'error statement
    On Error GoTo errHandler:
'check for values
    If Emp1.Value = "" Or Emp4.Value = "" Then
        MsgBox "There is no 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 this record", vbYesNo + vbDefaultButton2, "click OK to detete this record")
    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
       findvalue.EntireRow.Delete  'error line
    End If
'clear the controls
    cNum = 30
    For x = 1 To cNum
        Me.Controls("Emp" & x).Value = ""
    Next
'run the filter
    AdvFilterOutdata
'add the values to the listbox
    lstEmployee.RowSource = ""
    lstEmployee.RowSource = "Outdata"
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub

 

[Rfriend]

Based on what the Msgbox is returning you should be able to delete the row. Two tests:

Test 1. Temporarily replace this line:

 findvalue.EntireRow.Delete  'error line

with this:​

​

 findvalue.EntireRow.ClearContents  'error line

See if the code will clear the row's contents instead of deleting. Or will it give you the same error.​

​

Test 2. VBA Deletion test. Save your original code for Private Sub cmdDeleteEmp_Click() and temporarily replace it with this:

Private Sub cmdDeleteEmp_Click()
    Dim S As String
    Dim Ans As Variant
  
    Ans = InputBox("Choose a row number to delete a row", "Row Delete Test", 119)
    If Ans = "" Then
        Exit Sub
    End If
    With Sheet7.Range("A" & Ans) '(i.e "A119")
        S = "Cell A" & .Row & " con
[QUOTE="rlv01, post: 6040896, member: 403997"]
ased on what the Msgbox is returning you should be able to delete the row. Two tests:

Test 1. Temporarily replace this line:
         
[INDENT][CODE=vba] findvalue.EntireRow.Delete  'error line

[/INDENT]

with this:​

​

 findvalue.EntireRow.ClearContents  'error line

See if the code will clear the row's contents instead of deleting. Or will it give you the same error.​

​

Test 2. VBA Deletion test. Save your original code for Private Sub cmdDeleteEmp_Click() and temporarily replace it with this:

Private Sub cmdDeleteEmp_Click()
    Dim S As String
    Dim Ans As Variant
  
    Ans = InputBox("Choose a row number to delete a row", "Row Delete Test", 119)
    If Ans = "" Then
        Exit Sub
    End If
    With Sheet7.Range("A" & Ans) '(i.e "A119")
        S = "Cell A" & .Row & " contains the value '" & .Value & "'" & vbCr & vbCr
        S = S & "This action will delete the entire row (Row " & .Row & ")" & vbCr & vbCr & "Proceed?"
      
        Select Case MsgBox(S, vbYesNo, Application.Name)
            Case vbYes
                .EntireRow.Delete
        End Select
    End With
End Sub

See if you are able to delete the same row (119).

tains the value '" & .Value & "'" & vbCr & vbCr
S = S & "This action will delete the entire row (Row " & .Row & ")" & vbCr & vbCr & "Proceed?"

Select Case MsgBox(S, vbYesNo, Application.Name)
Case vbYes
.EntireRow.Delete
End Select
End With
End Sub[/code]
See if you are able to delete the same row (119).
[/QUOTE]


The first change did erase the data in the row, the second returned the error.

Based on what the Msgbox is returning you should be able to delete the row. Two tests:

Test 1. Temporarily replace this line:

 findvalue.EntireRow.Delete  'error line

with this:​

​

 findvalue.EntireRow.ClearContents  'error line

See if the code will clear the row's contents instead of deleting. Or will it give you the same error.​

​

Test 2. VBA Deletion test. Save your original code for Private Sub cmdDeleteEmp_Click() and temporarily replace it with this:

Private Sub cmdDeleteEmp_Click()
    Dim S As String
    Dim Ans As Variant
   
    Ans = InputBox("Choose a row number to delete a row", "Row Delete Test", 119)
    If Ans = "" Then
        Exit Sub
    End If
    With Sheet7.Range("A" & Ans) '(i.e "A119")
        S = "Cell A" & .Row & " contains the value '" & .Value & "'" & vbCr & vbCr
        S = S & "This action will delete the entire row (Row " & .Row & ")" & vbCr & vbCr & "Proceed?"
       
        Select Case MsgBox(S, vbYesNo, Application.Name)
            Case vbYes
                .EntireRow.Delete
        End Select
    End With
End Sub

See if you are able to delete the same row (119).

Test 1: Did remove the data.
Test 2 returned the error on ".EntireRow.Delete"

 

[Rfriend]

Based on what the Msgbox is returning you should be able to delete the row. Two tests:

Test 1. Temporarily replace this line:

 findvalue.EntireRow.Delete  'error line

with this:​

​

 findvalue.EntireRow.ClearContents  'error line

See if the code will clear the row's contents instead of deleting. Or will it give you the same error.​

​

Test 2. VBA Deletion test. Save your original code for Private Sub cmdDeleteEmp_Click() and temporarily replace it with this:

Private Sub cmdDeleteEmp_Click()
    Dim S As String
    Dim Ans As Variant
  
    Ans = InputBox("Choose a row number to delete a row", "Row Delete Test", 119)
    If Ans = "" Then
        Exit Sub
    End If
    With Sheet7.Range("A" & Ans) '(i.e "A119")
        S = "Cell A" & .Row & " contains the value '" & .Value & "'" & vbCr & vbCr
        S = S & "This action will delete the entire row (Row " & .Row & ")" & vbCr & vbCr & "Proceed?"
      
        Select Case MsgBox(S, vbYesNo, Application.Name)
            Case vbYes
                .EntireRow.Delete
        End Select
    End With
End Sub

See if you are able to delete the same row (119).

I added the Unprotect_All code for the sheet and got the same error in the same place. Because of the sort code that runs when you initiate the worksheet, the blank went to the bottom. Once I tried the 2nd code using row 126 instead of row 119, the row WAS deleted. (happy dance).

 

[rlv01]

Ok. Something about your worksheet structure on or near row 119 is causing issues. If that test code can delete row 126, it should also be able to delete row 119. What else is in row 119?
Do you happen to have a Table Header somewhere?

 

[Rfriend]

Ok. Something about your worksheet structure on or near row 119 is causing issues. If that test code can delete row 126, it should also be able to delete row 119. What else is in row 119?
Do you happen to have a Table Header somewhere?

I am not sure what you mean "Do I have a table header somewhere?"

The table (table735) has 34 columns. The advanced filter returns the data to table 4 which is a duplicate output and starts in column "AP". The code should only delete the table row, not the sheet row. There are no hidden columns or rows.

 

[rlv01]

I am not sure what you mean "Do I have a table header somewhere?"

The example I posted has a header for an Excel table in row 5. Your code:

  findvalue.EntireRow.Delete  'error line

which I am using, fails when I try to delete row 5 in exactly the same way that your code fails when you try to delete row 119. Now your first reaction may be to say "but there is no table header in that row!" which would miss the point of the example, which is that in certain conditions, worksheet contents/structure can cause the range.delete method to fail. And something is causing the operation to fail because there is nothing inherently wrong with using a statement like findvalue.EntireRow.Delete to delete a row.

The code should only delete the table row, not the sheet row.

Your code is deleting sheet rows, not table rows. That's what the 'EntireRow' portion means:

  findvalue.EntireRow.Delete  'error line

The table (table735) has 34 columns

Just to be clear on terminology, sometimes when people say "table" they mean a range of cell they are treating as a table. But a table is also a formal Excel object and referred to in VBA as a ListObject. Which are you referring to? I have not seen any references to a ListObject in your posted code.

 

[Rfriend]

The example I posted has a header for an Excel table in row 5. Your code:

  findvalue.EntireRow.Delete  'error line

which I am using, fails when I try to delete row 5 in exactly the same way that your code fails when you try to delete row 119. Now your first reaction may be to say "but there is no table header in that row!" which would miss the point of the example, which is that in certain conditions, worksheet contents/structure can cause the range.delete method to fail. And something is causing the operation to fail because there is nothing inherently wrong with using a statement like findvalue.EntireRow.Delete to delete a row.



Your code is deleting sheet rows, not table rows. That's what the 'EntireRow' portion means:

  findvalue.EntireRow.Delete  'error line

This is a table (ctrl T) named table735.
Just to be clear on terminology, sometimes when people say "table" they mean a range of cell they are treating as a table. But a table is also a formal Excel object and referred to in VBA as a ListObject. Which are you referring to? I have not seen any references to a ListObject in your posted code.

First, let me sincerely thank you for this in depth review. It has been educational and very generous on your part.

This is a table (ctrl T) named tanle735. There are 3 tables on this worksheet. Until this, everything works. The user form will search, load to the controls, edit the row, etc... But the delete has just been unbelievable. The reason for the command is so the user can delete an existing record before she is allowed to load a new employee into the vacant position number. This makes position number another unique data reference in addition to the record number and keeps staffing data at the actual number of positions allotted, in addition to reporting generated.

Is there a way for me to send you a copy, template, of the workbook?

 

[MARK858]

Assuming that you have a typo in your last post and tanle735 should be table735 as in your earlier post try the code below...

Sheet7.ListObjects("table735").ListRows(findvalue.Row - Sheet7.ListObjects("table735").HeaderRowRange.Row).Delete

 

[rlv01]

Now that we know we are working with a Table/ListObject, give @MARK858's suggestion a try.

 

[Rfriend]

Assuming that you have a typo in your last post and tanle735 should be table735 as in your earlier post try the code below...

Sheet7.ListObjects("table735").ListRows(findvalue.Row - Sheet7.ListObjects("table735").HeaderRowRange.Row).Delete

Mark858 -That solved my problem. Can you help me understand what I did wrong?

 

[Rfriend]

Now that we know we are working with a Table/ListObject, give @MARK858's suggestion a try.

That took care of it. THANK YOU BOTH so much for all the help. I need to understand what happened so I can learn from this.