CODEWITHSUNDEEP
vbaexcel
Thomas the plane
Thomas the plane

Reputation: 35

Code to delete a row in one Sub works but the other doesn't. "Delete Method of Range class failed"

I have several sub's that use this exact same code, but this one returns

Run-time error '1004':

Delete method of Range class failed

drill.Range("48:48").Delete

in the others it works just fine, but in this one it does not

Sub Drill_Log_Undo()
'
'takes the last submited drill log and places it back into the submission     line
'

'Unprotects Drill Log
    Worksheets("Drill Log").Unprotect "Welcome1"

'Declares Variables
    Dim drill As Worksheet: Set drill = Worksheets("Drill Log")
    Dim start_joint As Range: Set start_joint = drill.Range("C48")

    Dim start_time As Range: Set start_time = drill.Range("F48")
    Dim info As Range: Set info = drill.Range("G48:S48")
    Dim tool_used As Range: Set tool_used = drill.Range("E48")
    Dim pass_type As Range: Set pass_type = drill.Range("D48")

    Dim x As Integer
        x = 0

'coppies the data to the submission line
        drill.Range("G37:S37").Value = drill.Range("G48:S48").Value 'to Multi
        drill.Range("G35:S35").Value = drill.Range("G48:S48").Value 'to Single

'changes the pass type to the previously entered pass type
        drill.Range("K10").Value = pass_type.Value

'determins whether the pass was multiple or single
    If drill.Range("F48") = Null Or drill.Range("F48") = "" Then

    'adds the end joint to the end joint submission cell
        drill.Range("C37").Value = drill.Range("C48")

    'counts the number of joints and removes them as it goes
        Do Until drill.Range("A59").Value = Null

            If start_time.Value = "" Or Null Then

Here is the line that throws the code

                drill.Range("48:48").Delete

End line

                x = x + 1
                Set start_time = drill.Range("F48")

            Else
                drill.Range("F37").Value = drill.Range("F48").Value

                Exit Do

            End If

        Loop

    'adds the last joint to the joint count and removes the joint row
        drill.Range("B37") = drill.Range("C48")
        x = x + 1
        drill.Range("48:48").Delete


Else




End If

'protects Drill Log
    Worksheets("Drill Log").Protect "Welcome1"

End Sub

I have tried different ways of declaring the variables, I have even tried specifically calling the code by calling the workbook and worksheet in the line.

Upvotes: 0

Views: 43

Answers (1)

Moacir
Moacir

Reputation: 627

Replace drill.Range("48:48").Delete with drill.Rows("48").EntireRow.Delete

Upvotes: 1

Related Questions