Help with FOR Loop, not looping through sheets (Method 'Interset of object'_Application failed.)

[jmazorra]

Hello folks:

The procedure below is supposed to go through the 2 sheets and delete rows where the names in column I do not match the names in sheet repInformation column A3:A.

However, is only going through the first sheet in the Array and then getting an error in the highlighted line below. The error is: Method 'Interset of object'_Application failed.

Sub remove_Responsible()

Dim blnFound As Boolean
Dim rngDifferences As Range
Dim rngFound As Range
Dim rngName As Range
Dim rngToCheck As Range
Dim rngToDelete As Range
Dim lngCounter As Variant
Dim lngLastRow As Long
 
'\\Step 3
 
 'This procedure checks the names in column A in the repInformation sheet. If a
 'a name in column A is not in column I of the ticketInformation sheet, then
 'the entire row is deleted. We only want to count HR and HRSC reps, anyone else
 'can be deleted. The GetLastRow function is referenced in the public_Declarations module.
  
   
    Application.ScreenUpdating = False
    
    For Each WS In Sheets(Array("ticketInformation", "workPlans"))
        
        With WS
        
            lngLastRow = GETLASTROW(.Cells)
            
            'we don't want to delete our header row
            Set rngToCheck = .Range("I2:I" & lngLastRow)
        
        End With
        
        If lngLastRow > 1 Then
            With rngToCheck
                
                For Each lngCounter In Sheets("repInformation").Range("A3", _
                Sheets("repInformation").Cells(Rows.Count, 1).End(xlUp))
                
                Set rngFound = .Find( _
                                        What:=lngCounter.Value, _
                                        Lookat:=xlWhole, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=True)
                
                'check if we found a value we want to keep
                If Not rngFound Is Nothing Then
                    blnFound = True
                    
                    'if there are no cells with a different value then
                    'we will get an error
                    On Error Resume Next
                    
                    Set rngDifferences = .ColumnDifferences(Comparison:=rngFound)
                    
                    On Error GoTo 0
                    
                    If Not rngDifferences Is Nothing Then
                        
                        If rngToDelete Is Nothing Then
                            Set rngToDelete = rngDifferences
                        
                        Else
                            
[FONT=arial black][COLOR=#800000]                           Set rngToDelete = Application.Intersect(rngToDelete, rngDifferences)
[/COLOR][/FONT]                       
                        End If
                    End If
                End If
                Next lngCounter
            
            End With
        
        If rngToDelete Is Nothing Then
            If Not blnFound Then rngToCheck.EntireRow.Delete
        
        Else
            rngToDelete.EntireRow.Delete
        
        End If
    
    End If
    
Next WS
Application.ScreenUpdating = True
End Sub

Public Function GETLASTROW(ByVal rngToCheck As Range) As Long

'@Parameter rngLast finds the last row
'@Parameter GetLastRow function to call

Dim rngLast As Range
    
    Set rngLast = rngToCheck.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
        If rngLast Is Nothing Then
            GETLASTROW = rngToCheck.Row
        
                Else
                 GETLASTROW = rngLast.Row
                 
                    End If
    
End Function

 

[Jerry Sullivan]

Hi jmazorra,

The problem is that when rngToDelete is deleted in the first sheet, that reference no longer has a valid range object, but yet it will pass the test...

?Not rngToDelete is Nothing < returns True even after cells have been deleted>

A quick fix would be to reset the variable to Nothing after its Cells' EntireRows are Deleted and before moving to the next worksheet....

If rngToDelete Is Nothing Then
    If Not blnFound Then rngToCheck.EntireRow.Delete
Else
    rngToDelete.EntireRow.Delete
 [COLOR="#0000CD"]   [B]Set rngToDelete = Nothing[/B][/COLOR]
End If

 

[jmazorra]

Hi jmazorra,

The problem is that when rngToDelete is deleted in the first sheet, that reference no longer has a valid range object, but yet it will pass the test...

?Not rngToDelete is Nothing < returns True even after cells have been deleted>

A quick fix would be to reset the variable to Nothing after its Cells' EntireRows are Deleted and before moving to the next worksheet....

If rngToDelete Is Nothing Then
    If Not blnFound Then rngToCheck.EntireRow.Delete
Else
    rngToDelete.EntireRow.Delete
 [COLOR=#0000cd]   [B]Set rngToDelete = Nothing[/B][/COLOR]
End If

Thank you, that was exactly the issue.