VBA to hide column if another column contains a string?

Hi everyone, I'm hitting a roadblock with what is probably a simple fix, but I cannot figure out the syntax for it.

If any cell in column "I" contains the strong "Printer", I would like columns J thru M to be unhidden. If no cell in column "I" contains the string "Printer", then I would like the columns to be hidden.

I currently am using the following VBA, which unfortunately only works if a single cell (in this case, "I3") contains the string "Printer", rather than any cell in the entire column of "I":

Please Login or Register  to view this content.

I feel this needs to be some sort of "for each column in" type statement, but I'm unable to find any syntax that has worked.

Also, my column I contains a drop-down list. Is there any way to make the columns hide/unhide as soon as I choose "Printer" or something else from the list, rather than having to move to another cell for the columns to hide/unhide? Thanks everyone!!!!!

-Andrew

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

If you want a macro that you can run manually, try:

Please Login or Register  to view this content.

Thanks for the help Mumps! I tried the first macro so it would be dynamic/automatic, but nothing happened when I changed the value to or from "Printer" in any row in of column "I". I replaced my exiting macro with yours, of course. Does the macro work for you? It looks like it should do exactly what I want, from the best I can tell.

Originally Posted by Richard Buttrey

Hi,

I'd simplify this and create a 'check' cell that counts the number of occurrences of "Printer" in column I.
i.e. =

Formula:

Please Login or Register  to view this content.

Name the check cell say "check" then the macro is just

Please Login or Register  to view this content.

Thanks Richard! I am able to get my helper cell (the one with the COUNTIF formula) to change from 0 to 1 when selecting "Printer" in any cell within column I, but the other columns will not hide nor unhide when I manipulate the value in column I. I did also define a name of "check" for the helper cell, by the way.





Since neither of your guys' formulas are hiding the columns I'm wondering if there's something wrong on my end. I've restarted Excel several times (usually helps when macros stop working in a file) and no joy. I had a coworker try from his computer and it didn't work for him either. Anything I might be missing? Thanks again guys!

Hi Richard, thanks again for the help.

I think the method you used works just fine-- if the count of the word "Printer" in column "I" exceeds 0, then the VBA should unhide the columns. That's what I understand of your VBA and it makes total sense to me, I just have not been able to get the columns to automatically hide nor unhide with that. The counter does seem to increase from 0 just fine as I choose "Printer" from my drop-down list in multiple cells within column "I", so that part is working just fine, it's just that when I go from 0 to anything greater than 0, or from anything greater than 0 to 0, the columns will not hide/unhide dynamically.

I went back to the original macro I was using when I started this thread, and as long as I don't have "Printer" in cell I3 the macro hides the columns just fine, and when I change that value to "Printer" it unhides the columns perfectly. Obviously the only problem with that macro is it can only look in I3 instead of all of column I.

Your method should absolutely work, so I'm wording if the If Range("check") > 0 Then portion is not recognizing the name I defined ("check") within the Name Manager, on the cell with the COUNTIF formula, since the counter itself is working, and since you're using the same syntax to hide and unhide the cells, which works just fine in my original macro I posted.

Unfortunately our workstations at work prevent us from uploading any sort of files, so I can upload it when I get home later tonight if you'd like to review it. Thanks again Richard, I really appreciate it!

Originally Posted by Richard Buttrey

Would you clarify what you mean by 'selecting printer in any cell within a row'.

Oh and sorry, to clarify that, what I mean is I actually have a drop-down list used in all of column "I", and the goal is: if "Printer" is the value of any cell within column "I", then unhide columns J thru M.