Delete Method of Range Failed... But it works?

[HTMLGhozt]

Hello,

So... I've been working at this for quite a while. I think the code needs a closing statement for the for, but I'm not sure. When the error produces ".EntireRow.Delete" is highlighted; however, it still deleted the rows correctly. I'm trying to make this template neat, so it works, but it would be amazing if it didn't produce the run-time error. So, what would be the best way to fix this?

The macro finds "Term_DT" in the first row and deletes rows which do not contain "0" from that column. If there's a better way to do this please enlighten me.

Sub Carrier_STS(ws, Acell, rng, col, Lastrow, colName)


'Value for Deleting Terminated Employees
Dim DelTerm As Range
    Dim firstrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long




    With ws
            Set Acell = .Range("A1:ZZ1").Find(What:="Term_DT", LookIn:=xlValues, LookAt:=xlWhole, _
        MatchCase:=False, SearchFormat:=False)


    'If the column is found, then:
    If Not Acell Is Nothing Then
        col = Acell.Column
        colName = Split(.Cells(, col).Address, "$")(1)
        
        Lastrow = .Range(colName & .Rows.Count).End(xlUp).Row
        firstrow = .UsedRange.Cells(2).Row
            
        'This is the range of rows in the column specified.
        Set rng = .Range(colName & "2:" & colName & Lastrow)
            
     For Lrow = Lastrow To firstrow Step -1


            With .Cells(Lrow, colName)


                If Not IsError(.Value) Then
                    If .Value <> "0" Then .EntireRow.Delete


                End If
            End With
        Next
    Else
        MsgBox "Could not delete terminated employees!"
    End If
    End With
End Sub

 

[Gary McMaster]

Hi HTML

You could probably "cheat" and just add "On Error Resume Next" to the beginning of your code to suppress the error. Of course it is always better to prevent the error from occuring in the first place.

I'm not certain of the reason you are getting the "Delete Method of Range Failed". AutoFilters may be one reason.

Your code immediately changes most of the parameters you have passed into this procedure without ever using them first. This begs the question, "Why are you passing these parameters in the first place"

eg: Sub Carrier_STS(ws, Acell, rng, col, Lastrow, colName)

In any case, I find it more intuitive to work from top to bottom. The sample code below (built around your original code) uses "Union" to create a range of rows / cells that can be deleted after the loop terminates. You don't have to worry about addresses changing while you're in the loop which forces you to work from the bottom up. It also allows you to highlight the rows / cells that you intend to delete and give the user the opportunity to confirm or cancel the deletion.

Hope it helps.

Gary

In a standard module:

Option Explicit

Public Sub Test()

Carrier_STS ActiveSheet

End Sub

Sub Carrier_STS(ws As Worksheet) ', Acell, rng, col, Lastrow, colName) redefined below


'Value for Deleting Terminated Employees
'Dim DelTerm As Range
    'Dim firstrow As Long   'Not used in this procedure
    'Dim Lrow As Long       'Not used in this procedure
    'Dim CalcMode As Long   'Not used in this procedure
    'Dim ViewMode As Long   'Not used in this procedure
    
    Dim Acell As Range      ' from call parameters above
    Dim rng As Range        ' from call parameters above
    'Dim col As Long        ' from call parameters above
    Dim Lastrow As Long     ' from call parameters above
   ' Dim colName As String  ' from call parameters above
    
    Dim oTarget As Range
    Dim oDelete As Range
    
    With ws
    
        Set Acell = .Range("A1:ZZ1").Find(what:="Term_DT", LookIn:=xlValues, LookAt:=xlWhole, _
        MatchCase:=False, SearchFormat:=False)

    'If the column is found, then:
    If Not Acell Is Nothing Then
        
        Lastrow = ws.Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        Set oTarget = ws.Range(Acell.Offset(1, 0), .Cells(Lastrow, Acell.Column))
        oTarget.Interior.ColorIndex = 6 ' Debug line
        
        For Each rng In oTarget
            If rng.Value <> "0" Then
            
                If Not oDelete Is Nothing Then
                    Set oDelete = Union(oDelete, rng.EntireRow) ' Error if either range is nothing
                Else
                    Set oDelete = rng.EntireRow 'Add the first element found
                End If
                
            End If
        'col = Acell.Column
        'colName = Split(.Cells(, col).Address, "$")(1)
        
        'Lastrow = .Range(colName & .Rows.Count).End(xlUp).Row
        'firstrow = .UsedRange.Cells(2).Row
            
        'This is the range of rows in the column specified.
        'Set rng = .Range(colName & "2:" & colName & Lastrow)
            
     'For Lrow = Lastrow To firstrow Step -1


            'With .Cells(Lrow, colName)


                'If Not IsError(.Value) Then
                    'If .Value <> "0" Then .EntireRow.Delete
        Next rng
        
        oDelete.Interior.ColorIndex = 3 ' Debug line
        'oDelete.Delete

                'End If
            'End With
        'Next
    Else
        MsgBox "Could not delete terminated employees!"
    End If
    End With
    
End Sub

 

[HTMLGhozt]

I apologize for the mess of parameters, I've been trying a lot and haven't had time to clean it up. I'm pulling the parameters because they're used in about seven or eight subs in this module, so instead of a lot of repetitive information pulling the ranges seems easier. I haven't thought about actually pulling the value of the parameters, that may clean this up a bit. Regarding autofilters:

    ws.Unprotect
    ws2.Unprotect
    ws2.AutoFilterMode = False
    ws.AutoFilterMode = False

I have all this in the sub which is calling this sub. (Parent sub)... I think I might try the on error next... because the original code worked fully, it just populated an error.

I did try the standard module code and it caused a type mismatch on If rng.Value <> "0" Then this line.