Help with VBA - not working properly

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
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.

Code:
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this:
Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top