Custom Views under the View menu is a quick way to hide/show unwanted columns (among other things). There are two situation in which I find Custom Views useful: First, if there are certain columns that I want shown, but not printed; and second, if there are columns/rows that will be hidden in the final product, but I want to be able to quickly hide/unhide them during development.
Here’s a simple example: Create a custom view that shows everything and call it Develop.
Next, hide some columns and define another custom view, call it Final
You can use View > Custom Views to quickly toggle between the views. You can also add the Custom View Toolbar dropdown to one of your toolbars.
I used to like Custom Views, until I discovered that a custom view of a filtered list does not work very well with frozen panes.
To see what I mean:
My Column Header
a
b
c
d
freeze panes on “My Column Header” 1:1
filter on values = b
save as custom view
unfilter
apply that custom view
unfilter
now 1:2 are frozen – that’s annoying.
That is horrible! I think I better post about it.
Is there a way to make a form button (Activex Control Command Button perhaps?) that quickly hides rows, prints a selected area, and then unhides the rows? More simply, can you create/reference/enable custom views from VBA?
Found a way on my own. Here is some sample code:
With ActiveSheet
.Unprotect
HideRow = 0
Do
HideRow = HideRow + 1
Loop Until Range(“D7?).Offset(HideRow, 0) = “”
.Range(Rows(7).Offset(HideRow), Rows(38)).EntireRow.Hidden = True
With .PageSetup
.PrintArea = “$B$2:$U$45?
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.BlackAndWhite = True
.Orientation = xlLandscape
.CenterHorizontally = True
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
End With
.Protect
.PrintOut
.Unprotect
.Range(Rows(7).Offset(HideRow), Rows(38)).EntireRow.Hidden = False
ActiveWindow.Panes(2).ScrollRow = 39
.Protect
End With
Here’s a quicker range.
Highlight and define a NAME that covers the rows or columns that you want to hide/show.
Add a button (in my case I added a simple toggle checkbox), and use it to show/hide the rows or columns like this:
Private Sub chkToggle_Click()
Range(“MYDEFINEDRANGE”).EntireColumn.Hidden = Not chkToggle.Value
End Sub
Sweet!
Jeff