Selection count issue with pivot

[John_M13]

Hi,

I've been coming here through Google a lot lately and this has been a VERY useful source for excel solutions! But I've finally come across a unique problem I can't fix...so I'm hoping someone can help me with it!

In XL 2007 I'm currently using someone else's "Find" function to search for a keyword in a pivot table, then select them, highlight them, and dynamically create a legend for the keyword/color combination. I want to include the number of hits in the legend, i.e. "Reports (23)" but when I do Selection.Rows.Count I only get the number of selected rows that are contained within level 1 of the first pivot entry, and none after. So under the first pivot entry, John Smith, there may be 10 reports that contain the word "report" as well as 20 people after him, but 10 is the only count that is returned (though they are all successfully selected/highlighted).

Find Code

Function Find_Range(Find_Item As Variant, _
    Search_Range As Range, _
    Optional LookIn As Variant, _
    Optional LookAt As Variant, _
    Optional MatchCase As Boolean) As Range
 
    Dim c As Range, FirstAddress As String
    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False
 
    With Search_Range
        Set c = .Find( _
        What:=Find_Item, _
        LookIn:=LookIn, _
        LookAt:=LookAt, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=MatchCase, _
        SearchFormat:=False)
        If Not c Is Nothing Then
            Set Find_Range = c
            FirstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With
 
End Function

Selection Code

'Messy, but does the trick..Select the results
Find_Range(searchValue, Range("A1:B10000")).Select
 
'Set the legend entry to the search keyword and # of results
Range("G" & rownum).Value = searchValue & " (" & Selection.Rows.Count & ")"
 
'Show color dialog
Application.Dialogs(xlDialogPatterns).Show
 
'Grab the color of the colored results then set the legend entry to the same color
RGB = ActiveCell.Interior.color
Range("G" & rownum).Interior.color = RGB
 
Range("A1").Select 'Resets the selection
Range("I4").Value = Range("I4").Value + 2 'Adds 2 to the legend keeper to increment the next entry down 2 cells
Results.Hide

 

[Andrew Poulsom]

Does:

Selection.Count

or:

Selection.Cells.Count

give you the result you want?

 

[John_M13]

Ah, so simple! Yes it does Thank you.

I've just started VBA about a month ago and am literally learning as I go, so sometimes the little things still elude me! Much appreciated.