+ Reply to Thread
Results 1 to 4 of 4

Hide Column if Cell in that Column Equals Certain Value

  1. #1
    Registered User
    Join Date
    10-10-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    69

    Hide Column if Cell in that Column Equals Certain Value

    Hi there, in the attached spreadsheet there are a whole bunch of animals across the top row. What I want is a macro that will hide the columns of a cell that equal certain animals. So for example I want a macro that will hide the columns if the value in the top row of that column equals albatros, pelican and aligator. At the moment I am using this painful macro but it isn't efficient as I need to copy and paste each code statement for each column, which is insane:
    --------------------------
    Sub HideCol()

    If Range("A1") = "albatros" Or Range("A1") = "pelican" Or Range("A1") = "aligator" Then
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    If Range("B1") = "albatros" Or Range("B1") = "pelican" Or Range("B1") = "aligator" Then
    Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    End If

    End Sub
    --------------------------
    And this only does 2 columns and this just isn't flexible if I want to do heaps of columns or if I want to change an animal.

    Thanks for your support in advance, much appreciated!


    hide column.xlsx

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Hide Column if Cell in that Column Equals Certain Value

    See if this gets you where you want to be
    Please Login or Register  to view this content.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Registered User
    Join Date
    10-10-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Hide Column if Cell in that Column Equals Certain Value

    Mordred this works beautifully thanks so much. Sorry to be a pain but what would the macro be if instead of the animal data being in the top row it was on the 3rd row for example?

    Cheers,

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Hide Column if Cell in that Column Equals Certain Value

    Hi martinpgibson, no problem, notice the for each line and how the .Cells are different
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1