Unhide Row as Pivot Table Gets Updated

[adam]

The following code unhide’s the hidden rows and refreshes the pivot tables with looping. How could I make the code to unhide the rows without looping and also refresh the pivot tables?

Code: Select all

Private Sub Worksheet_Activate()

  Dim pvt As PivotTable
  Dim rw As Long
  Application.ScreenUpdating = False
  For Each pvt In Me.PivotTables
    pvt.RefreshTable
  Next pvt
  Application.Calculation = xlCalculationManual
    For rw = 48 To 1360
        If Not Range("A" & rw) = "" Then
            Range("A" & rw).EntireRow.Hidden = False
        End If
    Next rw
Application.Calculation = xlCalculationAutomatic
ScrollArea = "A1:AG1380"
Application.ScreenUpdating = True
End Sub

[HansV]

I don't think you can refresh the pivot tables without looping.
For the rows, you could try

Range("A48:A1360").SpecialCells(xlCellTypeConstants).EntireRow.Hidden = False

[adam]

Hans, Do you meant the final code would be as follows?

Code: Select all

Private Sub Worksheet_Activate()
  Dim pvt As PivotTable
  Application.ScreenUpdating = False
  For Each pvt In Me.PivotTables
    pvt.RefreshTable
  Next pvt
  Range("A48:A1360").SpecialCells(xlCellTypeConstants).EntireRow.Hidden = False
ScrollArea = "A1:AG1380"
Application.ScreenUpdating = True
End Sub

[HansV]

Why don't you try it?

[adam]

I've tried it.Thanks for the help

[adam]

The workbook in Post 22494 works excellent with its embedded code. But In doing so; it does hide the column containing the "Grand Total".

How could I make the code to prevent this?

[HansV]

The code contains two instances of the line

For c = 2 To m + 1

Change both to

For c = 2 To m - 1

[adam]

Thanks for the help. That was really helpful.

[adam]

The "Grand Total" Column does not get changed with month selection. How could it be made to change according to month selections?

[HansV]

You can't do that with the row totals of a pivot table - they sum all columns whether they are visible or not.

You'd have to turn off the row totals in the pivot tables, and provide your own formulas or code to calculate them.