VBA Finding matches occurring more than 2 times, then copy to new worksheet based upon multiple criteria

[jusmith]

I am new and trying to teach myself Excel VBA. I volunteered for a task and now think that vba can automate the task versus my doing it manually. The task is to find an account number occurring more than 2 times in Col A. If found, then if Col C AND Col D = "no" AND Col E = "yes" copy Col A, Col B, AND Col E to new worksheet. The worksheet contains 45000 rows of data. I am comfortable with IF statements but looping through rows and columns based specific criteria is beyond my understanding. Any assistance would be sincerely appreciated. I've attached an image of my table data

 

[Rijnsent]

Hi there,
welcome to the forum. If I understand you correctly, the only rows that matter are the ones with no,no,yes in column C,D&E? If so, start with a filter on those columns as that shortens your list considerably. Secondly, is this a task that you'd need to do often? If not, it might be much quicker to simply go with formulas. This code hopefully gets you started:

Sub FilterLoop()
    
    'Based on: https://newbedev.com/easiest-way-to-loop-through-a-filtered-list-with-vba
    
    'Remove any filters
    ActiveSheet.AutoFilterMode = False

    'Set your range including headers
    Set rRange = ActiveSheet.Range("A1:D10")
    'Set your criterias and filters one by one
    rRange.AutoFilter Field:=2, Criteria1:="no"
    rRange.AutoFilter Field:=3, Criteria1:="no"

    'Filter, offset(to exclude headers)
    Set filRange = rRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow

    For Each Rng In filRange
        'Looping over the visible rows
        Debug.Print Rng.Address
        
    Next

    'Remove any filters
    ActiveSheet.AutoFilterMode = False
End Sub

Cheers,
Koen

 

[jusmith]

Thank you so much, Koen. Yes, this routine will be used often so I thought going the VBA route would be more efficient. I sincerely appreciate your help. This is a great start!