Excel Geeking: Finding Out If A Range Intersects With PivotCache Source Date

As I was developing a utility to help clean up text for a co-worker, I ran into a error I have been since unable to replicate. Basically I could not get the native Find/Replace function in Excel to actually find and replace items if the range being modified was part of the source data for a pivot table.

So I embarked on a quest to determine if a selected range intersects with a pivot table’s data source, which is otherwise know as a pivot cache.

This should have been a lot simpler than it was. The primary issue is that, while you can read the SourceData property for a pivot cache into a variable, the property returns a string. Which means parsing the string, breaking it into its two parts (the worksheet name and the range address), converting the range address from R1C1 to A1 (since the SourceData property spits out as a R1C1 format), then setting the range variables and seeing if they intersect. Feels like a lot of work just to see if two ranges interset, but there it is. If there’s a better way to do it, I’m all ears.

The final routine is below. It’s set up as a sub, but with a couple of easy modifications it could (and probably should) be converted to a function returning a boolean. But I’ll leave that for you to do. Why should I have all the fun?

Here it is:

Private Sub DoesPivotCacheIntersect()
‘ Description:  This iterates through all the pivot caches in a workbook
‘               and determines whether the selected range intersects with them.

‘ Variable declarations.
Dim wb As Workbook
Dim sh As Worksheet
Dim pvtCache As PivotCache
Dim rSelection As Range
Dim rPivotData As Range
Dim lCnt As Long
Dim sPvtCache As String
Dim sShName As String
Dim sRngName As String

‘ Start by setting one range variable to our selection.
Set rSelection = Selection
‘ Also set the wb to the active workbook on which we’re using the utility.
Set wb = ActiveWorkbook

‘ Iterate through all the pivot caches in the workbook.
For Each pvtCache In wb.PivotCaches

‘ Set the string variable to the SourceData property value.
sPvtCache = pvtCache.SourceData

‘ We need to parse the source data to separate the sheet name from
‘ the range name. We’ll parse backwards, since a sheet name can contain
‘ and exclamation point, and an R1C1 range string cannot.
For lCnt = Len(sPvtCache) To 1 Step -1
If Mid(sPvtCache, lCnt, 1) = “!” Then
sShName = Left(sPvtCache, lCnt – 1)
sRngName = Right(sPvtCache, Len(sPvtCache) – lCnt)
Exit For
End If
Next lCnt

‘ If the sheet name is bracketed by apostrophes,
‘ then we need to trim them or else we’ll get an error
‘ we we try to set the range variable.
If Left(sShName, 1) = “‘” And Right(sShName, 1) = “‘” Then
sShName = Left(sShName, Len(sShName) – 1)
sShName = Right(sShName, Len(sShName) – 1)
End If

‘ We need to convert the R1C1 range that we get from the SourceData property.
‘ To do this, we’ll use ConvertFormula.
‘ We need to add an equal sign to make ConvertFormula think we are dealing
‘ with an actual formula.
sRngName = Application.ConvertFormula(“=” & sRngName, xlR1C1, xlA1)
‘ Once we’ve converted the range string to A1, we remove the equal sign.
sRngName = Replace(sRngName, “=”, “”)

‘ Finally, after much ado, set the range variable
Set rPivotData = wb.Worksheets(sShName).Range(sRngName)

‘ Now let’s see if we have an intersection.
If Not Intersect(rSelection, rPivotData) Is Nothing Then
MsgBox “Your selection intersects with a pivot cache in the active workbook.”, vbOKOnly
End If

Next pvtCache

End Sub