Need macro to hide column based on cell value

[kduschel]

I'm looking for a macro that will allow me to hide columns based on a cell value

I want the macro to HIDE all columns with "Hide" in Row1 and SHOW all columns that are "blank" in Row1. I've seen many posts on this topic, but I'm not skilled enough in this area to edit them to fit my situation.

Thanks in advance for your assistance!

KD

[jeffreybrown]

The basic idea would be

Please Login or Register  to view this content.

How do you want this to run?

[kduschel]

The intent was to create a macro button for the user. The sheet called "Worksheet" is pulling data from an Essbase retrieve. The user would refresh their retrieve and then run the "update" macro to hide the columns with no data. However, I welcome any better solutions.

[jeffreybrown]

So initially you said hide all the column with hide? Is the user going to enter the word hide?

Look at the post above and I updated the macro to the particular sheet you identified.

The best thing is to work with your data and see if this works for you, but not I don't necessarily have a better solution as I don't know your entire process.

I am calling in quits for tonight, but if you have more question maybe somebody else will step in, but if not I will be back tomorrow.

[kduschel]

Thanks, I'll give it a shot.

HIDE is populated by a formula. For example, if the period is Jan - June (or 1 thru 6), "HIDE" will be in the cell. If not, it will be blank. I'm trying to simply the process of rolling the schedule forward each month because not all users have the same knowledge of Excel.

I'll play with what you've given me and post additional questions if I get stumped...which is highly likely!!!

Thanks for taking the time to help!

KD

[Sa DQ]

Please Login or Register  to view this content.

[kduschel]

Okay....I got it to hide the column just fine. I suppose I will need the macro to "unhide" the column when the cell value changes back to "blank". This time I've mocked up an example. The yellow fields are the formulas that will determine if the column needs to be hidden. If it says "hide", the macro should hide it. If it is blank, the macro should leave it open and unhide any hidden columns. I tried editing the macro you gave me, but I was just guessing.

Thanks!
KD

[kduschel]

Thanks Sa DQ,

I just submitted a post before seeing yours. Thanks for the help! I take a look at it now!

[kduschel]

Both macros work to hide the columns based on my criteria, but I suppose I need it to unhide when/if the criteria changes. I'm not familiar enough with what has been given to me to know how to edit it to do the opposite.

Any guidance is greatly appreciated.

Thanks!
KD

[Winon]

See the attached WorkBook.

Hope that is what you want

[Winon]

Sorry, I sent you Excel 2007 version instead of Excel 2003

Here you go!

[kduschel]

Thank you very much, Winon. I have just a few newbie questions if you don't mind.

Dim i As Long and
Dim LC As Long

These 2 codes are setting the range, right? Does LC stand for Last Column? Does the i stand for anything or is it just a variable?

What does Application.ScreenUpdating = False mean?

Thank you all for your assistance!

KD

[Winon]

Hi kduschel,

You are welcome,

See this link: http://www.excelforum.com/excel-prog...ens-terms.html

What does Application.ScreenUpdating = False mean?

This is used to run the Code "in the background" without the user seeing how things are changing, or sometimes the Sreen "flickers". At the end of the Code the "False" is set to "True". Only then does the screen show you what the Code has done.