Quickly Hiding Columns

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.

Custview1_1

Custview2

Next, hide some columns and define another custom view, call it Final

Custview3

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.

Custview4

Posted in Uncategorized

5 thoughts on “Quickly Hiding Columns

  1. 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.

  2. 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?

  3. 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

  4. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.