+ Reply to Thread
Results 1 to 7 of 7

Excel VBA How to hide or show columns

  1. #1
    Registered User
    Join Date
    03-22-2021
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    2

    Excel VBA How to hide or show columns

    Hi all,

    I have a specific question and hopefully one of you can provide me with the correct code - I can't write VBA on my own though and I haven't found the right code to tweak yet.

    Problem (see attached file): I have a table (C12:I18) and I want to show certain columns based on a choice in one cell (B2). The matrix (C4:I7) states whether a column should be visible or not. One (1) means it is visible and zero (0) means it should be hidden. When I chosse 'All' in B2 then all columns are shown. When I choose 'Employment' then columns E and F should hide.

    I need flexibility in terms of adding more columns to the table and certainly my choices will increase (so the matrix will increase).

    Hope someone can help with code.

    Thanks, David
    Attached Files Attached Files

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Excel VBA How to hide or show columns

    Hi David & welcome to the forum

    Try below code based on your sample file ... Right click tab name --> view code --> paste below code then once you change cell B2 macro will work
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Excel VBA How to hide or show columns

    No loop option...
    Please Login or Register  to view this content.
    Last edited by sintek; 03-22-2021 at 09:20 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    03-22-2021
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    2

    Re: Excel VBA How to hide or show columns

    Hi nankw83, Thank you very much. I've implemented your code and it works brilliant.

    Hi sintek, Thank you very much too.

  5. #5
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Excel VBA How to hide or show columns

    Glad to help & thanks for the added Reputation points

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Excel VBA How to hide or show columns

    .........................
    Thanks.png

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! No loop but with columns flexibility !


    Quote Originally Posted by David Brimacomb View Post
    I need flexibility in terms of adding more columns to the table and certainly my choices will increase (so the matrix will increase).

    According to your attachment as a VBA starter :

    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
        
    Dim VA$
            If 
    Target.Address <> "$B$2" Then Exit Sub
            Application
    .ScreenUpdating False
            
    [A4].CurrentRegion.EntireColumn.Hidden False
            V 
    Application.Match(Target.Value2, [A4].CurrentRegion.Columns(2), 0)
        If 
    IsNumeric(VThen
            A 
    Range(Cells(V3), Cells(V2).End(xlToRight)).Address
            V 
    Filter(Evaluate("IF(" "=0,ADDRESS(1,COLUMN(" ")))"), FalseFalse)
            If 
    UBound(V) > -1 Then Range(Join(V",")).EntireColumn.Hidden True
        End 
    If
            
    Application.ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Show/HIde Columns using VBA
    By pdalal in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-13-2016, 04:03 PM
  2. Show / Hide columns
    By DBenson1968 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2014, 05:27 PM
  3. Excel 2008 : Hide/show columns
    By Didieeer in forum Excel General
    Replies: 1
    Last Post: 02-02-2012, 07:36 AM
  4. Hide columns before printing and show after
    By bagulhodoido in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-23-2011, 11:31 PM
  5. Hide Show columns
    By Glio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2007, 06:10 AM
  6. [SOLVED] how can hide and show columns using macro?
    By Hoshyar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2005, 11:05 AM
  7. [SOLVED] Show/Hide Columns
    By StephanieH in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-24-2005, 10:05 AM

Tags for this Thread

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