VBA Hide Columns Based on Date

  • Hey!

    I am very new to VBA, and macro coding. I have tried finding solutions for what I am attempting to do in excel.

    I am currently using the 2016 Mac Version of Excel with the project I am working on, so compatibility across platformed versions of Excel is a highly desired goal with this coding project.

    My Current Project is attempting to use a form button to toggle which columns are active (Unhidden) and those which are inactive (hidden). I already was able to create another button for the spreadsheet which toggles all columns to unhidden state, however I am having difficulty with creating the necessary VBA code for my other button.

    The Criteria is, the page automatically determines the current month by using the =month(today()) formula to display both text (cell B3) and the serial number of the month (cell B2).
    This information is important as it is the filter for the button toggle. The goal is to create a button which will hide all columns displaying months which are not the current month.

    The Column Ranges I have are Jan(C:C);Feb(D:D);Mar(E:E);Apr(F:F);May(G:G);Jun(H:H);Jul(I:I);Aug(J:J);Sept(K:K);Oct(L:L);Nov(M:M);Dec(N:N).

  • Re: VBA Hide Columns Based on Date

    Hi jbow11,

    Welcome to OzGrid!!

    See how this codes goes:



  • Re: VBA Hide Columns Based on Date

    How exactly would I input the VBA code properly?
    I opened the VBA editor panel, and attempted to put it under the Class Module opened by excel when I created the button.
    This is what my spreadsheet currently looks like:
    I'm sorry, I'm just really new to VBA (and to be brutally honest its been a few years since I've really done any real programming).

  • Re: VBA Hide Columns Based on Date

    I think my code didn't post quite correctly - see how it goes now.

    If you still have issues please post your workbook (devoid of any sensitive data) as screen shots don't help and are virtually impossible to read!!



  • Re: VBA Hide Columns Based on Date

    I noticed when deleted my macro before, I accidentally removed the macro for unhiding the hidden columns (just in case I wanted to compare previous months). Would you happen to know some VBA for opening up just those columns?

    Thank you very much for your help so far! It works exceptionally.

  • Re: VBA Hide Columns Based on Date

    You can unhide columns with a simple one liner like so:

    Sub UnhideAll()
        'Unhide columns C to N (inclusive)
        Range("C1:N1").EntireColumn.Hidden = False
    End Sub


  • Re: VBA Hide Columns Based on Date

    Ahh I see where my error was when I had tried to add your code. I was adding your code after the Sub CurrentMonth_Click () line.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!