Results 1 to 4 of 4

Hide columns based on header row color?

This is a discussion on Hide columns based on header row color? within the Excel Questions forums, part of the Question Forums category; I often need to hide the same columns in my Workbooks just for the purpose of printing them. To make ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    455

    Default

    I often need to hide the same columns in my Workbooks just for the purpose of printing them. To make them easy to recognize, I have the columns that I hide formatted blue in the header row and the ones I print are yellow. Is it possible to make a macro that would recognize the blue cells in row 1 and hide those columns? My right mouse button is getting worn out!

  2. #2
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,947

    Default

    FINALLY SOMEONE ELSE FROM ARIZONA!!!

    This macro will do the trick as long as you select a cell that has the color you wish to hide:

    Private Sub CommandButton1_Click()
    Dim ThisCell As Range
    Dim ThisColor As Long
    ThisColor = ActiveCell.Interior.Color
    For Each ThisCell In Range("A1:IV1")
    If ThisCell.Interior.Color = ThisColor Then
    ThisCell.EntireColumn.Select
    Selection.EntireColumn.Hidden = True
    End If
    Next ThisCell
    End Sub


    _________________
    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! ****************

    [ This Message was edited by: phantom1975 on 2003-02-06 15:08 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    Try something like the below. You could also use this in the Workbook BeforePrint event, with a line to check the sheet name before hand and exit the sub if the active sheet doesn't require columns to be hidden. Record a macro of you unhiding all the columns on a worksheet to reverse the process.

    Public Sub HideColumns()
    Dim rngColoured As Range
    Dim c As Range

    Set rngColoured = Range("A1", Range("IV1").End(xlToLeft))
    For Each c In rngColoured
    With c
    If .Interior.ColorIndex = 41 Then
    .EntireColumn.Hidden = True
    End If
    End With
    Next c

    End Sub

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Phoenix, Arizona
    Posts
    455

    Default

    Mudface - I modified your code slightly and placed it in the Sheet Object...

    Sub CommandButton1_Click()
    Dim rngColoured As Range

    Dim c As Range



    Set rngColoured = Range("A1", Range("IV1").End(xlToLeft))

    For Each c In rngColoured

    With c

    If .Interior.ColorIndex = 37 Then

    .EntireColumn.Hidden = True

    End If

    End With

    Next c



    End Sub

    When I run it in Excel2002 it works perfectly. However when I run the same workbook using Excel97 I get an 'Unable to Set the Hidden Property of the Range Class' error on the .EntireColumn.Hidden = True
    line. Can you tell me what I'm doing wrong?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com