Workaround to Hide only a Range of Rows ?

I'm using the below code to Show/Hide Navigation Buttons in a dashboard I am building. It nicely hides my navigation shapes/icons in columns A:B. In-order to do so all shapes/icons must be marked "Move and Size with cells".

Now for my problem, in building the dashboard there has become a need to hide various slicers that I have between rows 8:20 (to maximize the charting space) starting on column D. I have modified the ShowHideNavigation code to hide rows 8:20 and the slicers that reside in them but naturally because I have some Navigation Buttons residing in AB8:AB20 it's going to hide those navigation buttons when all i'm trying to do is hide the slicers.

I do not believe that you can hide a range of cells, so I was wondering if anyone had an idea for a clever workaround?

from http://datapigtechnologies.com/blog/index.php/creating-a-slide-out-menu-in-excel/
Sub ShowHideNavigation()
    Application.ScreenUpdating = False
            Dim ColumnRange As String
            Dim SmallestSize As Double
            Dim LargestSize As Double
            Dim Increment As Double
            Dim FromSize As Double
            Dim ToSize As Double
            Dim Stepper As Double
            Dim i As Double
             
            ColumnRange = "A:B"  '< --Change column to point to your columns
            SmallestSize = 0   '<--Adjust to the smallest column width needed
            LargestSize = 8   '<--Adjust to the largest column width needed
            Increment = 8      '<--Adjust to set the animation slower or faster
             
            Select Case ActiveSheet.Columns(ColumnRange).ColumnWidth
                Case Is <= SmallestSize
                    FromSize = SmallestSize
                    ToSize = LargestSize
                    Stepper = Increment
               
                Case Is > SmallestSize
                    FromSize = LargestSize
                    ToSize = SmallestSize
                    Stepper = -Increment
            End Select
             
            For i = FromSize To ToSize Step Stepper
                ActiveSheet.Columns(ColumnRange).ColumnWidth = i
                i = i
                DoEvents
            Next i
        ActiveSheet.Shapes("BTShowHideNavi").Flip msoFlipHorizontal
     Application.ScreenUpdating = True
End Sub

Open in new window

LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You can hide an entire row of cells, or entire column of cells.

You can toggle the shapes/buttons visibility with something like this

   
 
   ActiveSheet.Shapes.Range("5-Point Star 1").Visible = Not _
        ActiveSheet.Shapes.Range("5-Point Star 1").Visible

Open in new window


You can use a simple loop to work this on a group of buttons
-PolakAuthor Commented:
Hi Roy,

I am aware of the second method you mention; however, I don't believe that will help me maximize the charting space because simply hiding the slicers will not "bump up" the charts to the active view window.

You did just give me an idea tho.... could something be written for the SlicerShowHide macro to make the slicers in rows 8:20 NotVisible and then have pivot charts/pivot tables/tables/named ranges/etc... move up 12 rows (8 minus 20) then do the reverse and bump them back down?

EDIT: errr maybe not, i just noticed that you can't show/hide a slicer....?
Roy CoxGroup Finance ManagerCommented:
Can you have hidden rows that can be made visible when more space is required? Maybe use Custom Views
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

-PolakAuthor Commented:
Yes, that's my whole problem... again, I realize that I can hide the rows that the slicers are in (8:20) but that's also going to hide the navigation buttons that I have in Columns A8:B20.

Basically, I need to be able to independently hide the slicers in D8:Z20 but not affect the navigation pane I have in columns A:B.....
Roy CoxGroup Finance ManagerCommented:
Why not customize the Ribbon and have the buttons in your own Tab. That would be more professional as well.
-PolakAuthor Commented:
Heh, because I'm hiding the Ribbon by default to maximize screen real-estate. I also have never done that, and really don't know what the limitations are in terms of visual feel/display and export-ability if the dashboard is used on another machine...
-PolakAuthor Commented:
So, because slicer Objects can't be hidden how about this idea......

Place a White Fill Rectangle over the slicers in Rows 8:20, then moving up the pivot charts/pivot tables/tables/named ranges/etc content as previously described. Then, on show hiding the rectangle and bumping the content back down 12 rows?

If that sounds good the bit of code that I don't know how to write is how to move all of those object types up 12 rows.
Roy CoxGroup Finance ManagerCommented:
To move a PivotTable with VBA

 
ActiveSheet.PivotTables("PivotTable5").Location = "Pivot!$A$22"

Open in new window

Roy CoxGroup Finance ManagerCommented:
You can loop through the PivotTables and move them

Option Explicit

Sub MovePivots()
'
    Dim PT As PivotTable
    Dim iCol As Integer, iRow As Integer
    Dim addr As String

    iCol = 7
    iRow = 25

    For Each PT In ActiveSheet.PivotTables
        addr = Cells(iRow, iCol).Address
        PT.Location = "Pivot!" & addr
        iCol = iCol + 12
         Next PT
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
The Ribbon moves with the WorkBook, so if you had a DashBoard Tab with relevant buttons it should move with the workbook much as the old Menus did.
-PolakAuthor Commented:
I seem to be having a little bit of trouble adapting your code for Pivot Charts? How would you cycle though each one of those?
-PolakAuthor Commented:
Working a little more I got this far:
Sub ShowHideSlicers()
   'Application.ScreenUpdating = False
    Static EyeSwap As Boolean
    Dim RngToCover As Range
    Dim ChtOb As ChartObject
        EyeSwap = Not EyeSwap
        ActiveSheet.Shapes("ShowSlicers").Visible = EyeSwap
        ActiveSheet.Shapes("HideSlicers").Visible = Not EyeSwap
        
        If EyeSwap Then
            ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoFalse

            Set RngToCover = ActiveSheet.Range("D20")
            Set ChtOb = ActiveSheet.ChartObjects(1)
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
        Exit Sub
        End If
        
        If Not EyeSwap Then
            ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoTrue
            Set RngToCover = ActiveSheet.Range("D8")
            Set ChtOb = ActiveSheet.ChartObjects(1)
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
        End If
End Sub

Open in new window

Couldn't figure out how reference the charts in a For Each / Next , moreover the .Location didn't work as it did with your previous code.
Roy CoxGroup Finance ManagerCommented:
It looks like you can only move pivot charts to another sheet.

Here's a different ide. Why not create two dashboard sheets, one for each view. Then just have a button to switch sheets. I doubt anyone would notice.

I have a different method that I use, but it's difficult to explain on a Forum post. It involves having a hidden calculation sheets and displaying the charts and tables as images
-PolakAuthor Commented:
Images won't work because even after hiding the slicers there are a few slicers/macros left that can be pressed to manipulate the charts.

I could move to another worksheet though I'm worried about calculation time if I had to duplicate/synchronize any of the still visible slicers.

I'm confused though....the above code I posted works.... I just have to refer to the chartobjects as chartobjects(1), chartobjects(2), (3) etc... Or directly by using chartobjects("pivotable1") and I don't like hard coding the cell that they move to ala D8 and D20.

Is there no way around either of those issues?
Roy CoxGroup Finance ManagerCommented:
Can you attach an example workbook
-PolakAuthor Commented:
Difficult, because the data is in the PowerPivot Data Model and because it is sensitive. I could take some screenshots and blur out the actual data if you think that would be helpful?
Roy CoxGroup Finance ManagerCommented:
To loop through the PivotCharts try

 
Dim pCht As ChartObject

    For Each pCht In ActiveSheet.ChartObjects
        MsgBox pCht.Name
    Next pCht

Open in new window


See if this helps

 If EyeSwap Then
        ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoFalse

        Set RngToCover = ActiveSheet.Range("D20")
        For Each ChtOb In ActiveSheet.ChartObjects
            ChtOb.Top = RngToCover.Top      ' reposition
            ChtOb.Left = RngToCover.Left + iOffset    ' reposition
            iOffset = iOffset + ChtOb.Width + 10
        Next ChtOb
        Exit Sub
    End If

Open in new window

-PolakAuthor Commented:
Yep I can loop though the pivotcharts like that and that woulda worked if all of my charts were in the same horizontal plane. Unfortunately, some of them are stacked on top of eachother. See the below code.... it does everything I want for 3 of my charts, I still need to add 5 or 6 more chart objects into it... the only thing that kinda sucks is if I end up adding more slicers/playing around with their position i'll have to go back and manually add/subract rows from each ChtOb group. But that's not the worst thing in the world. Let me know if there is any way to improve it. I was thinking along the lines of IF statements for all charts with in an intersect and offsets based on the chart height.... not too sure how to code that though

If nothing can be done , I'll award points for helping me get the idea, thanks!

Sub ShowHideSlicers()
    Static EyeSwap As Boolean
    Dim RngToCover As Range
    Dim ChtOb As ChartObject
        EyeSwap = Not EyeSwap
        ActiveSheet.Shapes("ShowSlicers").Visible = EyeSwap
        ActiveSheet.Shapes("HideSlicers").Visible = Not EyeSwap

        If Not EyeSwap Then
            ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoTrue
            Set RngToCover = ActiveSheet.Range("D7")
            Set ChtOb = ActiveSheet.ChartObjects("PCHrsOpRegion")
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
            
            Set RngToCover = ActiveSheet.Range("D23")
            Set ChtOb = ActiveSheet.ChartObjects("PCHrsAssetSuit")
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
            
            Set RngToCover = ActiveSheet.Range("J7")
            Set ChtOb = ActiveSheet.ChartObjects("PCHrsPrioityReg")
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
        End If
         
        If EyeSwap Then
            ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoFalse
            Set RngToCover = ActiveSheet.Range("D20")
            Set ChtOb = ActiveSheet.ChartObjects("PCHrsOpRegion")
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
       
            Set RngToCover = ActiveSheet.Range("D36")
            Set ChtOb = ActiveSheet.ChartObjects("PCHrsAssetSuit")
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
            
            Set RngToCover = ActiveSheet.Range("J20")
            Set ChtOb = ActiveSheet.ChartObjects("PCHrsPrioityReg")
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
        End If
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
This might be easier to manage, Just add choices to the Chose functions as required.

Let me know if it works

Option Explicit

Sub ShowHideSlicers()
    Static EyeSwap As Boolean
    Dim RngToCover As Range
    Dim ChtOb As ChartObject
    Dim iX As Integer
    Dim sAddr As String, sObjName As String

    EyeSwap = Not EyeSwap

    ActiveSheet.Shapes("ShowSlicers").Visible = EyeSwap
    ActiveSheet.Shapes("HideSlicers").Visible = Not EyeSwap

    If Not EyeSwap Then
        ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoTrue
        For iX = 1 To 3
            sAddr = Choose(iX, "D7", "D23", "J7")
            sObjName = Choose(iX, "PCHrsOpRegion", "PCHrsAssetSuit", "PCHrsAssetSuit")
            Set RngToCover = ActiveSheet.Range(sAddr)
            Set ChtOb = ActiveSheet.ChartObjects(sObjName)
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
        Next iX
    End If

    If EyeSwap Then
        ActiveSheet.Shapes("HideSlicerRectangle").Visible = msoFalse
        For iX = 1 To 3
            sAddr = Choose(iX, "D20", "D36", "J20")
            sObjName = Choose(iX, "PCHrsOpRegion", "PCHrsAssetSuit", "PCHrsAssetSuit")
            Set RngToCover = ActiveSheet.Range(sAddr)
            Set ChtOb = ActiveSheet.ChartObjects(sObjName)
            ChtOb.Top = RngToCover.Top
            ChtOb.Left = RngToCover.Left
        Next iX
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Most def easier to manage! Thanks a bunch for bouncing this around with me. I think this is the best solution given my unique constraints to other approaches.

PS one of the PivotTable names in your code is duplicated (nobiggy) if you wanna edit
Roy CoxGroup Finance ManagerCommented:
I must have copied the wrong name, but you can see how it works.

Glad we came up with a working solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.