Hiding column using IF function?

  • Thread starter Thread starter gilbert
  • Start date Start date
G

gilbert

Can we use IF worksheet function to instruct Excel to hide the blank
column? For example, if cell D1 is blank, the column will automatically
dissapear from the eye of the user. D1 is depending on cell A1 (ie. D1
will blank if A1 condition is matched). Please advise....
 
Hi
without VBA and using the worksheet_change event this is not possible.
So there is no IF function to achieve this
 
Hi Frank,

Then, can we just simply add in VBA for this function? I have created
worksheet containing Excel functions, I want to remain that....jus
that I want to hide those blank columns using VBA, can we do that? Ca
you share with me how to write the simple VBA?

Thanks a lot!

Rgds,
Gilber
 
Hi Gilbert
you may provide some more detail
- will you invoke the procedure to hide the columns manually
- What should happen, if D1 contains info again

An other idea would be to use a filter (depends on the data you have in
your spreadsheet). So you should give some more details:
- sample rows
- your used formulas for A1/D1
 
Hi Frank,

Ok...here goes my scenario.

I am trying to create a Purchase orders control sheet for my cashflo
projections.

As I need flexibility for the worksheet, I allow them to define th
period for the control sheet depending on the info they want to view
(This I have used date function)

Maybe a clip will give u some picture.

A B C D E F
G H
1 PO # Vendor PO Date Terms Due Amount 1-Jan 2-Jan
2

Starting from G1 onward, the dates is running until the end of th
period that defined. As there will be some due amt after the period
that's why I added a column right the the last column of the workshee
as Amount Due after 30-Jun. The problem comes in if I tried to shorte
the period to one month say to show only from period 1-Jan-2004 t
31-Jan-2004. The running dates that I defined in row 1 can dissapea
when I changed the period but the blank column is still there...As
result, I need to scroll to the very last column just to see the tota
amount fall due after Jan 31. This give me the idea if I can ask Exce
to automatically hide those blank columns, I can easily get the whol
picture in one sheet, that is after the column with 31-Jan, I can se
my Amount Due After 31-Jan column. Of course, I need the flexibilit
there also when people defined a longer period, say 2 months, the
Excel should be able to reappear the hidden column up to 29-Feb.

Got the picture now? I need Excel to invoke automatically and don'
want the users to intervene manually. I also don't want to use filte
function b'coz the users may not be well understand with Excel...the
may simply corrupt the whole thing. Moreover, I think I can't us
filter in my scenario, can I?

So, I thought you could help me with some simple VBA which could d
that....I have zero knowledge on VBA...I start to pick up Exce
functions (the advance functions) when I join this forum. Thanks t
those experts who are so helpful just like you.

Of course I don't expect you to write for me the whole VBA....if th
VBA to perform this function is too tedious, I think I will forgo thi
idea b'coz no point wasting such a long time for this. Please show m
if only it is a short and simple VBA.

I hope I have clearly draw out the picture. Maybe you have othe
alternative ways to go about this automation thing, please share.

Thank you.

Rgds,
Gilber
 
Hi
before trying to solve this with VBA I would try other ways first:
Try grouping the days to months:
- add another heading row (e.g. the month names)
- use 'Data - Grouping' to allow users only to see specific months
or all of them

If you want to use VBA you can easily try this by yourself: Invoke the
macro recorder and hide some columns. Stop the macro recorder and have
a look at the code. But I think the grouping would be better for this
case
 
Hi Frank,

Sorry to bother u.....but I am not sure if I really get what you are
trying to show me....moreover, my dates will be varying depending on
the period the user defined. For example, if they define the period to
be from 1/2/2004 to 30/4/2004, then cell G1 will vary with starting
date of 1-Feb instead of 1-Jan. How can I group a month when the number
of days changes? Please help to explain to me.

I have never try using VBA before...I know I can record a Macro but how
to do get the macro run automatically without having the user to click
any Macro button? Furthermore, as the hiding of the column is not
standard, how do I tell Excel to hide which column and when to hide?
Please help me as I am really eager to be someone like you who is so
expert in Excel....

Thanks a zillion.


Rgds,
Gilbert
 
Back
Top