OK, I have a pivot table question, but this is more about general Excel layout than it is any heavy duty number crunching. I have 2 pivot tables, which house a lot of data. The pivot tables are linked by common slicers that I have added to the left of the pivot tables. A user would start from a high level slicer and then drill down into the data by selecting settings in subsequent slicers.
My predicament is that when all the data items are shown for each pivot table, there are at least 100 rows of data in the table. So, when both pivot tables are totally unfiltered, the 2nd pivot table is about 100 rows below the first. But, after all the filters in the slicers are selected, there could be only 5-10 items visible. If I don't do anything, then that 2nd pivot table is still 100 rows below the first pivot table, which is now only 5-10 rows long. So, there's a lot of "space space" and unnecessary scrolling from the 1st to the 2nd pivot table. What I'd really like to do is some Excel magic that moves the 2nd pivot table to just a few rows south of where the 1st pivot table ends, regardless of how many rows long that first pivot table is.
Does anyone know a relatively simple way to do this? I'm open to a VBA solution, which I believe would be required. One potential workaround would be to place that 2nd pivot table to the right of the 1st one. Each pivot table has a fixed number of columns, just a variable number of rows. It would just be more desirable to keep scrolling down the sheet to see more granular levels of detail rather than scroll continually from side to side.
Thanks in advance if anyone comes up with a creative solution. 🙂
Since most people have their monitors in landscape orientation in my experience, I'd probably put them side by side but, if you want them under each other, I'd just position them (with all the data displaying) so that there are a couple of blank rows in between, then use the Worksheet_PivotTableUpdate event to hide/unhide rows in between as the pivot tables change size.
As I understand it, that's exactly what Scotty wants Velouria, but (I think) not 'by hand' but by 'some Excel magic'.
I think about a button under the slicers with a text as 'Hide unnecessary blank rows' which activates a macro that 'automatically' does that trick for the user.
I'm not about VBA, so can't solve that, but think this is a solution Scotty maybe wants.
I'm afraid the solution with the pivot tables side by side don't work when you filter them for different items where the number of rows are different in both pivot tables.
Frans
Frans,
I assumed that, which is why I suggested using event code to automate the hiding/showing of rows.
I'm not clear on why side-by-side would be affected by different numbers of rows in the pivot tables? There wouldn't be any rows being hidden in that layout.
So, assuming a vertical layout, and two blank rows left between the fully expanded pivot tables, you could use something like this:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pvTop As PivotTable
Set pvTop = Me.PivotTables("PivotTable1")
' don't need to do anything if it's not the top pivot table updating
If Not Target.Name = pvTop.Name Then Exit Sub
On Error GoTo finally
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim pvBottom As PivotTable
Set pvBottom = Me.PivotTables("PivotTable2")
Dim LastHideRow As Long
LastHideRow = pvBottom.TableRange1.Row - 2
With pvTop.TableRange2
Dim FirstHideRow As Long
FirstHideRow = .Row + .Rows.Count + 1
Range(Cells(.Row, 1), Cells(LastHideRow, 1)).EntireRow.Hidden = False
End With
If LastHideRow >= FirstHideRow Then Range(Cells(FirstHideRow, 1), Cells(LastHideRow, 1)).EntireRow.Hidden = True
finally:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Wow, what a code. Not my cup of coffee, but I hope Scotty can work with that! Good work Velouria, thanks.
Frans
Velouria,
Yes! A VBA macro that updates when the pivot table is updated sounds like just the solution! I dabble in VBA, but I'll have to play with it to see how it works. Essentially, I'd need the code to figure out how many rows that first pivot table is so I know how many rows to hide to make it appear that the 2nd table is right under it. Thank you for providing that code example.
As far as Frans Visser's comments, I do think that a side by side solution would work too. And it's true that with large monitors, the side by side thing may not be that big a deal. Concerning the pivot tables, the 1st table will just contain summary data and inherently be somewhat short. The 2nd pivot table will contain details, and will always be longer than the first. But, that shouldn't be an issue when they are side by side.
Looking forward to test drive this solution. 🙂
