Auto-Hide Rows - Need Help RRS feed

  • Question

  • Hi, I have a VBA code to auto-hide rows between two PT that appear to be working but not 100% as I expected. 

    1)  It's unhiding hidden filters for both PT.  How do I get it to only hide/unhide the rows between the two PT ,therefore, the PT filters remain hidden.  

    2) I noticed that it's actually not running the macro for the correct tab.  I have two tabs..Slicer tab and Report tab.  After I make my selection on the Slicer tab, the macro auto-hids rows in the slicer tab!!  How do I make it so the macro runs on the Reports tab.  In the VBA editor screen, I have the code in the Reports tab.


    Sub Row_Hider()

    Dim lastFilledRow As Long
    Dim firstRowScndTbl

    Cells.EntireRow.Hidden = False

    firstRowScndTbl = 300

    lastFilledRow = Cells(firstRowScndTbl - 2, 2).End(xlUp).Row

    Range(Cells(lastFilledRow + 2, 2), Cells(firstRowScndTbl - 2, 2)).EntireRow.Hidden = True

    End Sub

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Call Row_Hider

    End Sub

    Friday, March 25, 2016 9:19 PM


  • Re:  multiple pivot tables and hiding rows

    Question 1...
      The line:  "Cells.EntireRow.Hidden = False"  unhides every row on the worksheet.
      Cells refers to all cells on the worksheet.

    Question 2...
     A. Identify the Target.Parent or the Target.Name in order to limit what gets worked on

     B. You need to identify the bottom row of the top table and the top row of the bottom table in order to hide the rows inbetween.
        "Target.TableRange1" (or 2) returns the range a pivot table occupies.

         Assuming "Rng" is the returned range object...
         Rng.Row   returns the first row of a range.
         Rng.Rows(Rng.Rows.Count).Row   returns the last row of a range.

    Jim Cone
    Portland, Oregon USA

    Sunday, March 27, 2016 2:59 PM