Run-time error '1004': Delete method of Range class failed

[UniGirl]

I am trying to delete rows that DON'T contain certain names, the code I currently have is:

Function IsMember(v As Variant, vArray As Variant) As Boolean
    Dim vLoop As Variant
    For Each vLoop In vArray
        If v = vLoop Then
            IsMember = True
            Exit Function
        End If
    Next vLoop
End Function

Sub Delete_Conslt()
    Dim lLast As Long, i As Long
    Dim vConsultants As Variant
    lLast = Cells(Rows.Count, "H").End(xlUp).Row
    vConsultants = Array("John Smith", "Julian Den", "Nick Richard", "Helen Walk", _
        "Mark White", "Jed Walton", "Emma Sheppard", "Jill Long", "Nei Turner")
    For i = lLast To 1 Step -1
               If Not IsMember(Cells(i, "H"), vConsultants) Then
            Cells(i, "A").EntireRow.Delete
         End If
    Next i
End Sub

The issue is that when I run this code it gets to the end and succesfully deletes the unwanted rows but then comes up with an error :

Run-time error '1004': Delete method of Range class failed

If i choose to debug it highlights the following code:

Cells(i, "A").EntireRow.Delete

I can't work out what the problem is, if i remove the NOT from the code and make it delete the rows i wish to keep the code works perfectly fine.

If anyone has any ideas that would be great!!

 

[XLfriend777]

Hi UniGirl - Welcome to the forum. Nice job with the code. I was able to get it to work with a few changes. Check out the code below.

NOTE: 1) A Variable name must Start with a letter and not a number.

Hope this helps.

Sub UniGirl_Delete_Rows()
    Dim lLast As Long, i As Long
    Dim vConsultants As Variant
    Last1 = Cells(Rows.count, "H").End(xlUp).Row
    vConsultants = Array("John Smith", "Julian Den", "Nick Richard", "Helen Walk", _
        "Mark White", "Jed Walton", "Emma Sheppard", "Jill Long", "Nei Turner")
    For i = Last1 To 1 Step -1
    Cells(i, 8).Select
'               If Not IsMember(Cells(i, "H"), vConsultants) Then
               If IsMember(Cells(i, "H"), vConsultants) Then
            Cells(i, "A").EntireRow.Delete
         End If
    Next i
End Sub

 

[UniGirl]

Hi goesr,

Thank you for the help, but this code also produces the same error. When the code has executed and it has actually carried out the command by removing all the correct rows it then Errors.

Highlighting the same line of code again:

Cells(i, "A").EntireRow.Delete

Any more help would be brilliant!

 

[XLfriend777]

Hi UniGirl - I'm sorry you are having that error. The code below works just fine for me when I test it with data in column H. I'm not sure why you would be having that error. If the variable i were ever to go to zero you would receive that error, but I don't see that occuring. You might try stopping i at 2 and see what happens. Hope this helps.

Function IsMember(v As Variant, vArray As Variant) As Boolean
    Dim vLoop As Variant
    For Each vLoop In vArray
        If v = vLoop Then
            IsMember = True
            Exit Function
        End If
    Next vLoop
End Function
Sub UniGirl_Delete_Rows()
    Dim lLast As Long, i As Long
    Dim vConsultants As Variant
    Last1 = Cells(Rows.count, "H").End(xlUp).Row
    vConsultants = Array("John Smith", "Julian Den", "Nick Richard", "Helen Walk", _
        "Mark White", "Jed Walton", "Emma Sheppard", "Jill Long", "Nei Turner")
    For i = Last1 To 1 Step -1
    Cells(i, 8).Select
               If Not IsMember(Cells(i, "H"), vConsultants) Then
'               If IsMember(Cells(i, "H"), vConsultants) Then
            Cells(i, "A").EntireRow.Delete
         End If
    Next i
End Sub