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

[Azuki]

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?

 

[patel45]

try these

LR1 = Cells(Rows.Count, "A").End(xlUp).Row
LR2 = ActiveSheet.UsedRange.Rows.Count

 

[Andrew Poulsom]

Example:

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

 

[Azuki]

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:

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

 

[Andrew Poulsom]

Try:

Sub Test()
    With ActiveSheet.PivotTables(1).TableRange2
        .Rows(.Rows.Count & ":200").Select
    End With
End Sub

 

[Azuki]

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.

 

[Jerry Sullivan]

Hi Azuki,


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

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.

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

 

[Azuki]

Hi Azuki,


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

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.

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.