Hiding Rows or Columns

[Scott]

I was wondering if it's possible to have a certain
column, say A, that you may want to have hidden depending
on the value of a cell, say B2. So for example, if B2=0,
then column A hides itself, but if B2=1, then column A is
displayed. Same question for row 1.
Thanks,
Scott

 

[Gord Dibben]

Scott

Only through Yikes! VBA

In a worksheet module which will run when you change value in B2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B2").Value = 0 Then
Columns(1).Hidden = True
Else
Columns(1).Hidden = False
End If
End Sub

In a general module when you run the macro from Tools>Macro>Macros.

Sub Hide_ColA()
If Range("B2").Value = 0 Then
Columns(1).Hidden = True
Else
Columns(1).Hidden = False
End If
End Sub

Frank gave you David's URL for "getting started with macros" in the other
post.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

See also David's pages on Events.

http://www.mvps.org/dmcritchie/excel/event.htm

Suggest you visit. You will open a new world of Excel if you lose the fear of
VBA and macros. The automation of Excel through VBA is a great tool.

Gord Dibben Excel MVP

 

[Scott]

Yikes is right. Not quite as easy as I thought. If I
start using macros, why do I get a warning about security
levels?

 

[A.W.J. Ales]

Scott,

If you are the same as Scott52 of Microsoft.Public.Excel.Misc. please do not
multipost and certainly not under different subjectnames.
If not : Sorry I disturbed this conversation.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Scott]

Not the same person.
Thanks!
Scott

-----Original Message-----
Scott,

If you are the same as Scott52 of

Microsoft.Public.Excel.Misc. please do not

 

[A.W.J. Ales]

Oke Scott.

As said : Sorry for the disturbance then.

However you migth take a look at it (Subject Conditional Formatting - Hiding
Rows; Scott52 at 10 feb at 18:04) as it contains a couple of suggestions to
your problem as well (from which the one of Dave Peterson is the better one
by the way) and by which you can avoid your "Yikes" VBA (which you
shouldn't avoid to log by the way ; it's really very usefull )

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Scott]

Found it.
Actually, I like your idea better. For me, it's all about
automation. Once I've got the file set up how I want, I
want to be able to change only one or two things to have
it customized with all of the changes. If I have to go in
and check if my target cell is 0 or 1, and then manually
hide the rows, that's two too many steps than I'd like to
perform.
My solution that I came up with is not really a
solution at all. I copied the worksheet in question, and
on the copy, the hid the columns and on the original kept
them displayed. That way I just print the appropriate
sheet and ignore the other.
Eventually I'll get into the whole macro thing, but
even basic excel is still new to me. I can climb a
staircase, but I can't jump straight to the top.
Regards,
Scott