VBA to List each count of instances of a given value in another column

[mfitzgerald94]

Hello,

I've used advanced filtering to give me the unique values in column F and have those values displayed in column M using the code below.

    Columns("F:F").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "M:M"), Unique:=True

I would now like to use VBA to display a "countif" for the number of instances each value that's in column M appears in column F. I know that's kind of an awkward sentence but I couldn't think of a better way to word it.

Example:

Column F Column M Column N
Jon Jon 2
Jon Sally 3
Sally Jen 1
Sally Mike 2
Sally Sam 2
Jen
Mike
Mike
Sam
Sam


Any ideas? Thanks for your help!

[AlvaroSiza]

You could do it through arrays or collections, but this should be a bit easier to follow:

Sub CountNames()

Dim rngUnique As Range
Dim rngSource as Range
Dim rngCell As Range
Dim lngCount As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
        Columns("F:F").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
            "M:M"), Unique:=True
            
        With ActiveSheet
            Set rngUnique = .Range("M1", .Range("M1").End(xlDown))
            Set rngSource = .Range("F1", .Range("F1").End(xlDown))
        End With
        
        For Each rngCell In rngUnique
            lngCount = Application.WorksheetFunction.CountIf(rngSource, rngCell)
            rngCell.Offset(, 1) = lngCount
        Next rngCell
           
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

[mfitzgerald94]

Alvaro, that was perfect. Thank you for your help. I don't suppose you could give me a break down of what these sections do and how they do it? I'm still new to using VBA but I'm finding it very useful.

With ActiveSheet
            Set rngUnique = .Range("M1", .Range("M1").End(xlDown))
            Set rngSource = .Range("F1", .Range("F1").End(xlDown))
        End With
        
        For Each rngCell In rngUnique
            lngCount = Application.WorksheetFunction.CountIf(rngSource, rngCell)
            rngCell.Offset(, 1) = lngCount
        Next rngCell

[AlvaroSiza]

Sure, no problem at all.

Referring to first block, I'm simply telling excel via the Set syntax of the Range object, what to refer to later in the code. In this case, Column M represents the result of your 'unique' filter. End(xlDown) simply replicates hitting Shift+Down on your keyboard when within the application itself. So long as there are no blanks, it is a way to define a range by selecting the first cell and letting Excel find the last. I then repeated this for the "F" column values. We now have two ranges to work with.

The second piece basically says, for each cell in the range containing only my unique names ("rngUnique" via Set rngUnique described above), perform the function countif, with the unique name as argument and the complete, raw, non-filtered range as the range to be queried. Assign the result of the countif to a Long variable. Then, in the cell one to the right of the unique name (Column N), place the value contained in the Long variable.

I suppose this 2nd piece could be streamlined to:

rngCell.Offset(, 1).Value= Application.WorksheetFunction.CountIf(rngSource, rngCell)

which would lessen the code by one line and the use of one variable, which if not to reused over and over again, is likely the better practice.

Hope this helps,

[mfitzgerald94]

Okay, awesome. Thanks so much for the detailed explanation. That helps a lot.