Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: hide columns

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is there a macro genius out there who knows how to automate hiding columns in excel, please? i have a large-ish spreadsheet with many columns which may or may not be used depending on user input, and i want to hide the unused columns so they don't print / take up screen space. is it possible to hide columns dependent on the contents of cells i.e. "if cell H3 on sheet 1 is blank, hide columns F, H, and K on sheet 2"
    thanks in advance

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This should work OK for you, right-click on your sheet1 tab, select view code and paste in the following: -

    Private Sub Worksheet_Change(ByVal Target As Range)

    If [H3].Value = "" Then
    Sheet2.Range("F:F,H:H,K:K").Columns.Hidden = True
    End If

    End Sub

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, this might be better: -

    Private Sub Worksheet_Change(ByVal Target As Range)

    If [H3].Value = "" Then
    Sheet2.Range("F:F,H:H,K:K").Columns.Hidden = True
    Else:
    Sheet2.Range("F:F,H:H,K:K").Columns.Hidden = False
    End If

    End Sub

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks

    hope it stops raining soon

  5. #5
    New Member
    Join Date
    Jun 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: hide columns

    I have a similar problem in that I wish to hide a specific column based on the contents of one cell in that column.

    The spreadsheet covers 12 columns, with a total column at the extreme right. Not all the columns are used every time so the total column ends up some way from the last column to be completed. I would like to find a way of formatting each column so that, if it contains no data, it is hidden.

    Is it possible to write a code which applies to an individual column or do I have replicate the above code for each column in turn?

    Thanks a lot,
    John

Some videos you may like

User Tag List

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
  •