Hide or Unhide Column Based on Cell Value

I want to come up with an easy to have my Excel file (on open) evaluate the
first row of data and hide the columns where there is none yet (future
months of data will fill in later and then auto unhide).

If possible it would be even better to evaluate the column and make sure Max
of cells is 0 then I know that no cell in the whole column is filled in.
Then if it is 0, hide if not 0 unhide.

Just don't know the syntax.

Tried...

Sub HideAndSeek()
'
' HideAndSeek Macro
'
If Range("H4").Value2 Is Null Then

Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Else
Columns("H:H").Select
Selection.EntireColumn.Hidden = False
End If

End Sub


But get errors on the way I try to evaluate the cell value.

Any help would be greatly appreciated.

c-

Two issues:

(1) you probably want to put the commands in an AUTO OPEN macro, so they
are executed automatically when you open the file.

(2) I suspect you need to think through the process that must be
accomplished before you worry about syntax... because it is not clear to
me what you want done.

For example... If column C in Row 1 is blank, do you want to hide
Column C even if Column C contains data in Row 2? Perhaps there is not
data in any row other than Row 1.

You write that it would be better to "evaluate the column." The
statement implies that there is more than one row of data. If there is
more than one row of data, do you really mean to only examine the first
row of data?

WindsurferLA


ChrisR wrote:
> I want to come up with an easy to have my Excel file (on open) evaluate the
> first row of data and hide the columns where there is none yet (future
> months of data will fill in later and then auto unhide).
>
> If possible it would be even better to evaluate the column and make sure Max
> of cells is 0 then I know that no cell in the whole column is filled in.
> Then if it is 0, hide if not 0 unhide.
>
> Just don't know the syntax.
>
> Tried...
>
> Sub HideAndSeek()
> '
> ' HideAndSeek Macro
> '
> If Range("H4").Value2 Is Null Then
>
> Columns("H:H").Select
> Selection.EntireColumn.Hidden = True
> Else
> Columns("H:H").Select
> Selection.EntireColumn.Hidden = False
> End If
>
> End Sub
>
>
> But get errors on the way I try to evaluate the cell value.
>
> Any help would be greatly appreciated.
>
> c-
>
>

Chris,

It almost sounds like you are looking for No entries in column C?
If Worksheetfunction.Counta(Range("C:C") = 0 than
"your code here"
Else
"alternate code here"
end if

Or maybe you are looking for numeric entries Only:
use "Count" instead of "Counta"

The Max function will return the max value in the column.

hth...

--
steveB

Remove "AYN" from email to respond
"windsurferLA" <[email protected]> wrote in message
news:[email protected]...
> Two issues:
>
> (1) you probably want to put the commands in an AUTO OPEN macro, so they
> are executed automatically when you open the file.
>
> (2) I suspect you need to think through the process that must be
> accomplished before you worry about syntax... because it is not clear to
> me what you want done.
>
> For example... If column C in Row 1 is blank, do you want to hide Column C
> even if Column C contains data in Row 2? Perhaps there is not data in any
> row other than Row 1.
>
> You write that it would be better to "evaluate the column." The statement
> implies that there is more than one row of data. If there is more than
> one row of data, do you really mean to only examine the first row of data?
>
> WindsurferLA
>
>
> ChrisR wrote:
>> I want to come up with an easy to have my Excel file (on open) evaluate
>> the
>> first row of data and hide the columns where there is none yet (future
>> months of data will fill in later and then auto unhide).
>>
>> If possible it would be even better to evaluate the column and make sure
>> Max
>> of cells is 0 then I know that no cell in the whole column is filled in.
>> Then if it is 0, hide if not 0 unhide.
>>
>> Just don't know the syntax.
>>
>> Tried...
>>
>> Sub HideAndSeek()
>> '
>> ' HideAndSeek Macro
>> '
>> If Range("H4").Value2 Is Null Then
>>
>> Columns("H:H").Select
>> Selection.EntireColumn.Hidden = True
>> Else
>> Columns("H:H").Select
>> Selection.EntireColumn.Hidden = False
>> End If
>>
>> End Sub
>>
>>
>> But get errors on the way I try to evaluate the cell value.
>>
>> Any help would be greatly appreciated.
>>
>> c-
>>

Thanks Much. Worked like a charm. Didn't know about how to use the
worksheetfunction in VBA. That will help me in some other projects I am
working on as well.

c-
"STEVE BELL" <[email protected]> wrote in message
news:rNfXf.29$Up2.26@trnddc07...
> Chris,
>
> It almost sounds like you are looking for No entries in column C?
> If Worksheetfunction.Counta(Range("C:C") = 0 than
> "your code here"
> Else
> "alternate code here"
> end if
>
> Or maybe you are looking for numeric entries Only:
> use "Count" instead of "Counta"
>
> The Max function will return the max value in the column.
>
> hth...
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "windsurferLA" <[email protected]> wrote in message
> news:[email protected]...
> > Two issues:
> >
> > (1) you probably want to put the commands in an AUTO OPEN macro, so they
> > are executed automatically when you open the file.
> >
> > (2) I suspect you need to think through the process that must be
> > accomplished before you worry about syntax... because it is not clear to
> > me what you want done.
> >
> > For example... If column C in Row 1 is blank, do you want to hide Column
C
> > even if Column C contains data in Row 2? Perhaps there is not data in
any
> > row other than Row 1.
> >
> > You write that it would be better to "evaluate the column." The
statement
> > implies that there is more than one row of data. If there is more than
> > one row of data, do you really mean to only examine the first row of
data?
> >
> > WindsurferLA
> >
> >
> > ChrisR wrote:
> >> I want to come up with an easy to have my Excel file (on open) evaluate
> >> the
> >> first row of data and hide the columns where there is none yet (future
> >> months of data will fill in later and then auto unhide).
> >>
> >> If possible it would be even better to evaluate the column and make
sure
> >> Max
> >> of cells is 0 then I know that no cell in the whole column is filled
in.
> >> Then if it is 0, hide if not 0 unhide.
> >>
> >> Just don't know the syntax.
> >>
> >> Tried...
> >>
> >> Sub HideAndSeek()
> >> '
> >> ' HideAndSeek Macro
> >> '
> >> If Range("H4").Value2 Is Null Then
> >>
> >> Columns("H:H").Select
> >> Selection.EntireColumn.Hidden = True
> >> Else
> >> Columns("H:H").Select
> >> Selection.EntireColumn.Hidden = False
> >> End If
> >>
> >> End Sub
> >>
> >>
> >> But get errors on the way I try to evaluate the cell value.
> >>
> >> Any help would be greatly appreciated.
> >>
> >> c-
> >>
>