Help with VBA - not working properly

[jmazorra]

Hello folks:

I need some assistance with the procedure below. I have the names in the array that I want to keep. However, instead of having that array, I want the varList variable to look at range A2:A in sheet repInformation, and use that list to compare.

Sub test()

    Dim varList As Variant
    Dim lngLastRow As Long, lngCounter As Long
    Dim rngToCheck As Range, rngFound As Range
    Dim rngToDelete As Range, rngDifferences As Range
    Dim blnFound As Boolean
    
    Application.ScreenUpdating = False
    
    With Sheets("ticketInformation")
        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
        
        
             
            varList = VBA.Array("Smith, Mark", "Brown, Terry)
            
            For lngCounter = LBound(varList) To UBound(varList)
 
                Set rngFound = .Find( _
                                        What:=varList(lngCounter), _
                                        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
                            Set rngToDelete = Application.Intersect(rngToDelete, rngDifferences)
                        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
 
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Try this:

Sub test2()
    Dim varList As Variant
    Dim lngLastRow As Long, lngCounter As Range    
    Dim rngToCheck As Range, rngFound As Range
    Dim rngToDelete As Range, rngDifferences As Range
    Dim blnFound As Boolean
    Application.ScreenUpdating = False
        With Sheets("ticketInformation")
            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("A2", _
                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
                            Set rngToDelete = Application.Intersect(rngToDelete, rngDifferences)
                        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
    Application.ScreenUpdating = True
End Sub

 

[jmazorra]

I just had to change the Dim lngCounter from Range to Variant. It worked perfectly, I knew I was close, but could not figure it out.

thank you much.

 

[JLGWhiz]

I just had to change the Dim lngCounter from Range to Variant. It worked perfectly, I knew I was close, but could not figure it out.

thank you much.

Don't understand the change to variant, but if works, good!

Regards, JLG