How do I get to the last row of pivot table?

Azuki

Board Regular
Joined
Feb 14, 2013
Messages
73
I have 2 pivot tables. 1 on top of the other. Depend on how user select the rows, the top pivot may use less rows if only certain fields are chosen. so I want to hide those rows that are blank, so that user can see the bottom pivot. I can't specify the rows to be hidden, because it depends how many rows the top pivot will use, depending on user's selection. So I want to hide rows right after last row of top pivot till just before the bottom pivot. How do i get to the last row of a pivot table?
 
try these
Code:
LR1 = Cells(Rows.Count, "A").End(xlUp).Row
LR2 = ActiveSheet.UsedRange.Rows.Count
 
Upvote 0
Example:

Code:
Sub Test()
    With ActiveSheet.PivotTables(1).TableRange2
        MsgBox "Last row is: " & .Row + .Rows.Count - 1
    End With
End Sub
 
Upvote 0
Thanks, but how do you select a range of rows?
Like & .Row + .Rows.Count - 1 to row 200.
Range (& .Row + .Rows.Count - 1, "200").select
doesn't seems to work.


Example:

Code:
Sub Test()
    With ActiveSheet.PivotTables(1).TableRange2
        MsgBox "Last row is: " & .Row + .Rows.Count - 1
    End With
End Sub
 
Upvote 0
Try:

Code:
Sub Test()
    With ActiveSheet.PivotTables(1).TableRange2
        .Rows(.Rows.Count & ":200").Select
    End With
End Sub
 
Upvote 0
I am almost close. I have this.


[code ]Sub HideNewBizRows()
With ActiveSheet.PivotTables("YTDNewBizComm").TableRange2
newBizLastRow = .Cells(.Cells.Count).Row
End With



With ActiveSheet.PivotTables("YTDComm").TableRange2
.Rows(.Rows.Count + 1 & ":129").Select
End With


Selection.EntireRow.Hidden = True


End Sub[/code]

Instead of hard coding row 129. I want to replace it with newBizLastRow. But I just can't get the syntax right. :(

Can someone please help? Basically, I'm trying to select the blank rows between end of top pivot and start of bottom pivot and hide those rows.
 
Last edited:
Upvote 0
Hi Azuki,


This code would hide all rows between the two pivots (replace with the names of your pivots).

Code:
Sub HideRowsBtwnPivots()
    '--Hides all rows between two PivotTables.
    
    Dim lPivot1LastRow As Long, lPivot2FirstRow As Long
    
    '---Get last row of top PivotTable
    With ActiveSheet.PivotTables("PivotTable1").TableRange2
        lPivot1LastRow = .Row + .Rows.Count - 1
    End With
    
    '---Get first row of bottom PivotTable
    With ActiveSheet.PivotTables("PivotTable2").TableRange2
        lPivot2FirstRow = .Row
    End With
    
    '--Hide rows in-between
    If lPivot1LastRow + 1 < lPivot2FirstRow Then _
        Rows(lPivot1LastRow + 1 & ":" & lPivot2FirstRow - 1).Hidden = True
End Sub

If those Pivots are stacked in the same columns, you will probably want to leave at least one row between.
Here is some code to let you specifiy a variable number of rows.

Code:
Sub HideAllButXRowsBtwnPivots()
    '--Hides blank rows between two PivotTables.
    
    Dim lPivot1LastRow As Long, lPivot2FirstRow As Long
    Const lRowsBtwn As Long = 12
    
    '---Get last row of top PivotTable
    With ActiveSheet.PivotTables("PivotTable1").TableRange2
        lPivot1LastRow = .Row + .Rows.Count - 1
    End With
    
    '---Get first row of bottom PivotTable
    With ActiveSheet.PivotTables("PivotTable2").TableRange2
        lPivot2FirstRow = .Row
    End With
    
    '--UnHide rows in-between
    If lPivot1LastRow + 1 < lPivot2FirstRow Then _
        Rows(lPivot1LastRow + 1 & ":" & lPivot2FirstRow - 1).Hidden = False
    
    '--Hide all but specified number of rows in-between
    If lPivot1LastRow + 1 + lRowsBtwn < lPivot2FirstRow Then
        Rows(lPivot1LastRow + 1 + lRowsBtwn & _
        ":" & lPivot2FirstRow - 1).Hidden = True
    End If
End Sub
 
Last edited:
Upvote 0
Hi Azuki,


This code would hide all rows between the two pivots (replace with the names of your pivots).

Code:
Sub HideRowsBtwnPivots()
    '--Hides all rows between two PivotTables.
    
    Dim lPivot1LastRow As Long, lPivot2FirstRow As Long
    
    '---Get last row of top PivotTable
    With ActiveSheet.PivotTables("PivotTable1").TableRange2
        lPivot1LastRow = .Row + .Rows.Count - 1
    End With
    
    '---Get first row of bottom PivotTable
    With ActiveSheet.PivotTables("PivotTable2").TableRange2
        lPivot2FirstRow = .Row
    End With
    
    '--Hide rows in-between
    If lPivot1LastRow + 1 < lPivot2FirstRow Then _
        Rows(lPivot1LastRow + 1 & ":" & lPivot2FirstRow - 1).Hidden = True
End Sub

If those Pivots are stacked in the same columns, you will probably want to leave at least one row between.
Here is some code to let you specifiy a variable number of rows.

Code:
Sub HideAllButXRowsBtwnPivots()
    '--Hides blank rows between two PivotTables.
    
    Dim lPivot1LastRow As Long, lPivot2FirstRow As Long
    Const lRowsBtwn As Long = 12
    
    '---Get last row of top PivotTable
    With ActiveSheet.PivotTables("PivotTable1").TableRange2
        lPivot1LastRow = .Row + .Rows.Count - 1
    End With
    
    '---Get first row of bottom PivotTable
    With ActiveSheet.PivotTables("PivotTable2").TableRange2
        lPivot2FirstRow = .Row
    End With
    
    '--UnHide rows in-between
    If lPivot1LastRow + 1 < lPivot2FirstRow Then _
        Rows(lPivot1LastRow + 1 & ":" & lPivot2FirstRow - 1).Hidden = False
    
    '--Hide all but specified number of rows in-between
    If lPivot1LastRow + 1 + lRowsBtwn < lPivot2FirstRow Then
        Rows(lPivot1LastRow + 1 + lRowsBtwn & _
        ":" & lPivot2FirstRow - 1).Hidden = True
    End If
End Sub


Thanks a lot! This work perfectly! Exactly what I needed. :)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top