Run Time Error 1004

[amoverton2]

Hi all!

So out of the blue I'm starting to get a run-time error with this code (Run Time Error 1004: Select Method of Range class failed) that used to have no issues... Can someone tell me why, or how to fix or change the code???

Note: I did try doing the suggested ways of fixing it to no avail via other posts and other online posts

Private Sub cmdDELETE_Click()
Dim account As String
Dim r As Long


If cmbDELNAME.Text = "" Then
    MsgBox "Enter Account Number"
End If

account = Trim(cmbDELNAME.Text)

r = 2
    Worksheets("O_ProspectiveGain_Add").activate
    Do While Worksheets("O_ProspectiveGain_Add").Cells(r, 2) <> ""
    If Worksheets("O_ProspectiveGain_Add").Cells(r, 2).Value = account Then
        Worksheets("O_ProspectiveGain_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_Sponsor_Add").activate
r = 2
    Do While Worksheets("O_Sponsor_Add").Cells(r, 2) <> ""
    If Worksheets("O_Sponsor_Add").Cells(r, 2).Value = account Then
        Worksheets("O_Sponsor_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop
Worksheets("O_AdminInfoGain_Add").activate
r = 2
    Do While Worksheets("O_AdminInfoGain_Add").Cells(r, 2) <> ""
    If Worksheets("O_AdminInfoGain_Add").Cells(r, 2).Value = account Then
        Worksheets("O_AdminInfoGain_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_LastContact_Add").activate
r = 2
    Do While Worksheets("O_LastContact_Add").Cells(r, 2) <> ""
    If Worksheets("O_LastContact_Add").Cells(r, 2).Value = account Then
        Worksheets("O_LastContact_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_TravelInfo_Add").activate
r = 2
    Do While Worksheets("O_TravelInfo_Add").Cells(r, 2) <> ""
    If Worksheets("O_TravelInfo_Add").Cells(r, 2).Value = account Then
        Worksheets("O_TravelInfo_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_FamilyInfo_Add").activate
r = 2
    Do While Worksheets("O_FamilyInfo_Add").Cells(r, 2) <> ""
    If Worksheets("O_FamilyInfo_Add").Cells(r, 2).Value = account Then
        Worksheets("O_FamilyInfo_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

Worksheets("O_MiscNotes_Add").activate
r = 2
    Do While Worksheets("O_MiscNotes_Add").Cells(r, 2) <> ""
    If Worksheets("O_MiscNotes_Add").Cells(r, 2).Value = account Then
        Worksheets("O_MiscNotes_Add").Cells(r, 2).Select
        ActiveCell.EntireRow.Delete Shift:=xlUp
        End If
        r = r + 1
   Loop

    MsgBox "Member Deleted"
    
    ThisWorkbook.Save
    MsgBox "Saved"
    
End Sub

Thank you!!!

 

[bebo021999]

1) Not 100% surely but
Do While Worksheets("O_ProspectiveGain_Add").Cells(r, 2) <> ""
change to:
Do While Worksheets("O_ProspectiveGain_Add").Cells(r, 2).value <> ""

2) Normally delete rows in range, loop from bottom to top, like this:

For i = 100 to 1 step -1

Or

r = 100
Do while ...
...
r=r-1

3) If it was still not solved, which line of code was highlighted yellow in debug mode?

 

[amoverton2]

Debugger highlights: Worksheets("O_ProspectiveGain_Add").Cells(r, 2).Select

I've retyped it out and tried again and then it skips the above and highlights the next: Worksheets("O_Sponsor_Add").Cells(r, 2).Select

 

[bebo021999]

Try to set sheet active first, then select.

 

[Zot]

When a row is deleted, the cursor stays at the same location, but the next row data already shifted to the current cursor location. Then your program move cursor to next row, thus you are skipping line each time you deleted a row. You need to delete from last row down to avoid skipping

 

[Alex Blakenburg]

Zot beat me to that comment. How about trying this:-

Private Sub cmdDELETE_Click()
    Dim account As String
    Dim r As Long
    Dim arrSht() As Variant
    Dim sht As Worksheet
    Dim shtName As Variant
    Dim rngToDelete As Range
    
    arrSht = Array("O_ProspectiveGain_Add", "O_Sponsor_Add", "O_AdminInfoGain_Add", _
                    "O_LastContact_Add", "O_TravelInfo_Add", "O_FamilyInfo_Add", "O_MiscNotes_Add")
    
    If cmbDELNAME.Text = "" Then
        MsgBox "Enter Account Number"
    End If
    
    account = Trim(cmbDELNAME.Text)
    
    For Each shtName In arrSht
        r = 2
        Set rngToDelete = Nothing
        Set sht = Worksheets(shtName)
        
        Do While sht.Cells(r, 2) <> ""
            If sht.Cells(r, 2).Value = account Then
                If rngToDelete Is Nothing Then
                    Set rngToDelete = sht.Cells(r, 2)
                Else
                    Set rngToDelete = Union(rngToDelete, sht.Cells(r, 2))
                End If
                
            End If
            r = r + 1
        Loop
        rngToDelete.EntireRow.Delete
    Next shtName

    MsgBox "Member Deleted"
    
    ThisWorkbook.Save
    MsgBox "Saved"
    
End Sub

 

[Zot]

Although @Alex Blakenburg or me did not answer your question directly, you need to try Alex code first. Maybe your error will just go away. ?

The code just collect all the rows to be deleted first and delete them in one go. This is much faster and the method I preferred instead of deleting one by one from behind. Note also that you should avoid activate sheet. This will slow down execution and avoid headache when troubleshooting. Define and refer to each sheet directly like how Alex did.

 

[amoverton2]

Cool,

Tried it, now I have a run-time error 91: object variable or with block variable not set.

no debugger option on this one...

 

[Alex Blakenburg]

Was the workbook with the code in it the ActiveWorkbook when you ran the code ?

 

[Alex Blakenburg]

I think I know what it is, my guess is that it is not finding the Account specified on one or more of the sheets.
Try
Replacing this

  rngToDelete.EntireRow.Delete

With this

        If Not rngToDelete Is Nothing Then
            rngToDelete.EntireRow.Delete
        End If