Database Record Deleting

[Robert Allan]

I am creating a Data Entry Form in Excel and I am having a problem deleting a record. My Subs Save, Modify and Reset are all working fine, but I keep getting the Runtime error 1004 “method of range class failed”. The following is my sub-routine:

Sub DeleteRecord()
    Dim iRow As Long
    Dim iRec As Long
           iRec = Application.InputBox("Please enter Rec No. to delete the record.", "Delete Record?", , , , , , 1)
                    If iRow = 2 then
                         iRec = 1
                   End If
    On Error Resume Next
    iRow = Application.WorksheetFunction.IfError(Application.WorksheetFunction.Match(iRec, Sheets("Data 2020").Range("A:A"), 0), 0)
               On Error GoTo 0
                If iRow = 0 Then
                    MsgBox "No record found", vbOKOnly + vbCritical, "No Record"
                 Exit Sub
              End If
    Sheets("Data 2020").Cells(iRow, 1).EntireRow.Delete shift:=xlUp
End Sub

Please Help!

 

[Trebor76]

Hi Robert,

Welcome to MrExcel!!

Are all the entries in Col. A of the Data 2020 tab unique or can there be duplicates?

Robert

 

[Peter_SSs]

Welcome to the MrExcel board!

I keep getting the Runtime error 1004 “method of range class failed”.

1. What line of code is this occurring on if you 'Debug'?

2. Where is this code located (Standard module?, Worksheet module & if so which worksheet?, ThisWorkbook module? Another workbook?)

BTW, please use code tags when posting code (to preserve indentation formatting etc) as code that is all left-aligned is much harder to read & debug. My signature block below has help with that. I have fixed it for you on this occasion.

 

[Robert Allan]

Hi Robert,

Welcome to MrExcel!!

Are all the entries in Col. A of the Data 2020 tab unique or can there be duplicates?

Robert

There is no duplicates. Col. A is the record number.

 

[Robert Allan]

Welcome to the MrExcel board!

1. What line of code is this occurring on if you 'Debug'?

2. Where is this code located (Standard module?, Worksheet module & if so which worksheet?, ThisWorkbook module? Another workbook?)

BTW, please use code tags when posting code (to preserve indentation formatting etc) as code that is all left-aligned is much harder to read & debug. My signature block below has help with that. I have fixed it for you on this occasion.

If this is what you mean by what line is occurring in, the it is on line 340 of the module "Sheets("Data 2020").Cells(iRow, 1).EntireRow.Delete shift:=xlUp".
And also if this is what you mean, it is Sheet 11(Data 2020), my database.
Sorry! but I am pretty green at this stuff.

 

[Peter_SSs]

Thanks for the additional information.

Runtime error 1004 “method of range class failed”

Is that the exact error message?

Or is it this?

Is worksheet 'Data 2020' protected?

 

[Robert Allan]

Thanks for the additional information.

Is that the exact error message?

Or is it this?

View attachment 10134

Is worksheet 'Data 2020' protected?

Sorry I missed the Delete. it is Delete method of range class failed”.

 

[Peter_SSs]

Sorry I missed the Delete. it is Delete method of range class failed”.

Thanks for the clarification on that.

.. and the answer to my other question?

 

[Trebor76]

Try this where the record numbers populate a list box called "ListBox1" which the user selects (clicks on) the relevant record to delete and processes their selection by clicking a command button called "CommandButton1":

Option Explicit
Private Sub UserForm_Activate()
    
    Dim lngLastRow As Long
    
    lngLastRow = Sheets("Data 2020").Cells(Rows.Count, "A").End(xlUp).Row
    
    Me.ListBox1.List = Sheets("Data 2020").Range("A2:A" & lngLastRow).Value
    
End Sub
Private Sub CommandButton1_Click()
     
     Dim i As Long
     Dim lngDeleteRow As Long
     
     With Me.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                If IsNumeric(.List(i)) = True Then
                    lngDeleteRow = Evaluate("IFERROR(MATCH(" & CDbl(.List(i)) & ",'Data 2020'!A:A,0),0)")
                Else
                    lngDeleteRow = Evaluate("IFERROR(MATCH(""" & CStr(.List(i)) & """,'Data 2020'!A:A,0),0)")
                End If
            End If
        Next i
    End With
    
    If lngDeleteRow > 0 Then
        If MsgBox("You have selected to delete record " & lngDeleteRow & " from the database." & vbNewLine & "Are you sure?", vbYesNo + vbExclamation) = vbYes Then
            Sheets("Data 2020").Rows(lngDeleteRow).Delete
        End If
    End If
     
End Sub

Regards,

Robert

 

[Robert Allan]

Problem Solved!

I thank all those that tried to help me with my problem, and Trebo76 made a excellent attempt, but it didn’t work. I finally got a little frustrated at it. So I started from the top, and re-wrote the entire program. I still don’t know what was causing the error, but it worked fine the second time around. I guess that’s the way programming goes.

But thanks again to everybody for trying.