How to hide rows based on an If/Then logical statement?

[red_deer]

Question 1:

I would like to Hide rows based on an If/Then statement
Cell A1 has the value of "1" or "2" (from a drop down list). If A1 has the value of "1", the rows 2:4 will be hidden. If it has other values, the rows 2:4 will be unhidden.

Question 2:
I tried the following VBA code. It is unclear to me why it doesn't work. How to correct it? Can it be used as the answer to Question 1 (if corrected)?
-------------------------------------------------------------------------------------

Please Login or Register  to view this content.

-------------------------------------------------------------------------------------
Thanks in advance for any clarification.
Best regards,
RD

[JapanDave]

Some very crude code that will do what you want.

Please Login or Register  to view this content.

Whoops, I had code to hide columns. Fixed it now.

[red_deer]

Thanks, Dave.

What I really want is as follows: Once I select the value [1,2] the droplist (data/VALIDATION), the response of hiding rows 2:4 will automatically occur. This is a one-step operation.

However, if using "sub", we may need to further click a command to drive/kick off the sub (i.e. "dave()"). So, it then becomes a two-step operation.

How do you think? Do we have any way to make the "sub" execute automatically upon selecting a value from the drop-list? If yes, then how?

In my last post, the idea of using "function" (combined with "sub") is a way attempting to realize the one-step operation. But I am uncertain whether it is a legal solution, because I am not sure whether it respects "grammar" of Excel.

Please advise. Thanks.

RD

[JapanDave]

Oh, my mistake.

Try this code, but make sure you right click Sheet1 and click veiw code, then paste this code in that window. Then try changing the value in cell A1. It should automatically change for you.

Please Login or Register  to view this content.

[foxguy]

This does the same as JapanDave. Just a different variation
I did this on the fly with no testing, so back up first.

Please Login or Register  to view this content.

[foxguy]

Wanted to correct a statement I made.
There is such a thing as EntireColumn.Value, just not the way you used it.

Please Login or Register  to view this content.

This puts the values in the columns that contain <SomeRange> into an array in variable a.
You probably wouldn't want to use it like this because it would be a LARGE array, but it is possible.

[mikerickson]

Please Login or Register  to view this content.

[JapanDave]

Now why can't I have 3 people respond to my threads when asking for help???