I was pouring through Héctor Miguel Orozco Díaz’s filtering code recently and decided to rewrite it. I was hoping to make it shorter, but I couldn’t. I did move some code outside the main procedure, though, and I’ll be posting that code over the next few days.

In order to filter the source range of a pivot table, I had to know where that range lives. And that’s the subject of today’s post. The PivotTable object has a SourceData property that is a String representation of the Excel range. Upstream of this function, I test to make sure the pivot table is based on an Excel range, so you won’t see any of that code here.

The objective here is to parse the SourceData string and convert it into a Range object. I pass in the PivotTable as an argument. The SourceData property returns something that looks like this:

Sheet1!R1C1:R51C4

I don’t know why it uses R1C1 notation, but there it is. My first step is to convert it that to A1 notation with the ConvertFormula method, which returns something like this:

[PivotTableGroup.xls]Sheet1!$A$1:$D$51

I don’t need the workbook name because you can’t have the source in a different workbook than the pivot table unless you use External Data. But it’s there, so I have to deal with it. Now I just need to parse the string. I’m looking for two key characters in the string: The bang operator (!) separates the sheet name from the range reference, and the closing bracket (]) identifies the end of the workbook name. I attempt to find both of those characters’ positions and store them in lBang and lClose.

I could probably safely assume that there will always be a bang and a close bracket, but I test for it anyway. If there’s no bang, I assume the whole string is just a range reference. If there is a bang, I parse out the sheet name and the range reference and store them. If there’s no close bracket, I assume the string is in the format Sheet1!$A$1:$D$51. I haven’t actually found an instance where both weren’t there, but I didn’t know (and still don’t know for sure) that that would always be the case.

With the sheet name and range reference, I get the range like this:

The Parent of a PivotTable is a Worksheet. The Parent of that is the Workbook. Now I can get that source range into a variable in the calling procedure. That looks like this:

In the next post I’ll create an array of all the pivots that affect a particular cell within a pivot table.