Error 1004 - Delete method of range class failed

R

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
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

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

Attachments

  • Capture.PNG
    Capture.PNG
    72.5 KB · Views: 9
Sort by date Sort by votes
Also Replace this:
VBA Code: 
    Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
        End If

With this (for debug purposes):
VBA Code: 
        Dim S As String
        S = "Emp1 Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Addr:   " & Emp1.Address & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End If

Report back on what the MsgBox says.
1679963024677.png
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Also Replace this:
rlv01 said:
VBA Code: 
    Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
        End If

With this (for debug purposes):
VBA Code: 
        Dim S As String
        S = "Emp1 Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Addr:   " & Emp1.Address & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End If

Report back on what the MsgBox says.
View attachment 88486
Click to expand...

Should Em1.address be declared and set to something as in you previous example (D2)?
rlv01 said:
Capture7.PNG
Click to expand...
 
Upvote 0
rlv01 said:
Also Replace this:
VBA Code: 
    Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
        End If

With this (for debug purposes):
VBA Code: 
        Dim S As String
        S = "Emp1 Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Addr:   " & Emp1.Address & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End If

Report back on what the MsgBox says.
View attachment 88486
Click to expand...
I changes "/address to .value" and receoved the following msg:
1680005084980.png
 
Upvote 0
MARK858 said:
@Rfriend, What happens with the change below?

Rich (BB code): 
    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=CLng(Emp1), LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
       End If
    End If
Click to expand...
I am so sorry, I missed this post. Errors at the code change suggested CLng(Emp1).

See below.

1680010699351.png
 
Upvote 0
Rfriend said:
Should Em1.address be declared and set to something as in you previous example (D2)?
Click to expand...
I have no way of knowing this because Emp1 is not defined anywhere in your code. It just appears like magic. You have to remember that while you are familiar with your code I am just making guesses from the bits of information you provide.
Rfriend said:
I changes "/address to .value" and receoved the following msg:
Click to expand...
Why did you have to change .address to value?
 
Upvote 0
Rfriend said:
I changes "/address to .value" and receoved the following msg:

1680005084980-png.88510
Click to expand...

That seems like good news. It tells us the value being searched for is "111" ( a string) and the value found in column A was 111 (a double) in row 119. It means that the next line of code:
VBA Code: 
            findvalue.EntireRow.Delete  'error line

should have deleted row 119, assuming your worksheet is not protected. Did it?

And if it did not, what happened instead?
 
Upvote 0
rlv01 said:
That seems like good news. It tells us the value being searched for is "111" ( a string) and the value found in column A was 111 (a double) in row 119. It means that the next line of code:
VBA Code: 
            findvalue.EntireRow.Delete  'error line

should have deleted row 119, assuming your worksheet is not protected. Did it?

And if it did not, what happened instead?
Click to expand...

Same error. Asked me if I was sure , gave me the msgbox as before, then errored 1004. Just to recall, I had to change the Emp1.Address to Emp1.Value to remove the error noted in response #23.

1680015857102.png
 
Upvote 0
Ok, here is some new debug code.

Replace this
VBA Code: 
        Dim S As String
        S = "Emp1 Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Addr:   " & Emp1.Address & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End I

with this
VBA Code: 
        'Debug Code
        Dim S As String
        On Error Resume Next
        S = "Emp1 Value Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Object Type: " & TypeName(Emp1) & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        On Error GoTo 0
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            On Error Resume Next
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address(, , , 1) & vbCr
            S = S & "findvalue row Addr:   " & findvalue.EntireRow.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value & vbCr
            S = S & "findvalue parent:   " & findvalue.Parent.Name & vbCr
            S = S & "findvalue protection:   " & findvalue.Parent.ProtectContents & vbCr
            S = S & "Active Sheet:   " & ActiveSheet.Name & vbCr
            S = S & "Active Cell:   " & ActiveCell.Address(, , , 1) & vbCr
            On Error GoTo 0
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End If
    End If

Report back on what the MsgBox says.
 
Upvote 0
rlv01 said:
Ok, here is some new debug code.

Replace this
VBA Code: 
        Dim S As String
        S = "Emp1 Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Addr:   " & Emp1.Address & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End I

with this
VBA Code: 
        'Debug Code
        Dim S As String
        On Error Resume Next
        S = "Emp1 Value Type: " & TypeName(Emp1.Value) & vbCr
        S = S & "Emp1 Object Type: " & TypeName(Emp1) & vbCr
        S = S & "Emp1 Value:   " & Emp1.Value & vbCr & vbCr
        On Error GoTo 0
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
            On Error Resume Next
            S = S & "Record found " & vbCr
            S = S & "findvalue Type:   " & TypeName(findvalue.Value) & vbCr
            S = S & "findvalue Addr:   " & findvalue.Address(, , , 1) & vbCr
            S = S & "findvalue row Addr:   " & findvalue.EntireRow.Address & vbCr
            S = S & "findvalue Value:   " & findvalue.Value & vbCr
            S = S & "findvalue parent:   " & findvalue.Parent.Name & vbCr
            S = S & "findvalue protection:   " & findvalue.Parent.ProtectContents & vbCr
            S = S & "Active Sheet:   " & ActiveSheet.Name & vbCr
            S = S & "Active Cell:   " & ActiveCell.Address(, , , 1) & vbCr
            On Error GoTo 0
            MsgBox S
            findvalue.EntireRow.Delete  'error line
        Else
            'Not Found
            S = S & "Cannot find record "
            MsgBox S
            Exit Sub
        End If
    End If

Report back on what the MsgBox says.
Click to expand...

Method of range class failed:
1680026863671.png
 
Upvote 0
Based on what the Msgbox is returning you should be able to delete the row. Two tests:

Test 1. Temporarily replace this line:

VBA Code: 
 findvalue.EntireRow.Delete  'error line
with this:​
​
VBA Code: 
 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:
VBA Code: 
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).
 
Upvote 0
You must log in or register to reply here.

Similar threads

sharky12345
  • Question Question
Error 1004 - Copy method of Worksheet class failed
Replies
1
Views
135
yoseeeph
Y
R
  • Question Question
Delete from 2 tables at same time
Replies
2
Views
388
Rfriend
R
D
  • Solved
Error 1004
2
Replies
18
Views
401
dmt32
D
M
  • Question Question
Error 1004 - Application defined or object defined error.
Replies
4
Views
64
MeaclH
M
B
  • Solved
Error on method intersect
Replies
2
Views
136
Beneindias
B

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

Share this page

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