Hide or Unhide Column Based on Cell Value
 

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-

Hide or Unhide Column Based on Cell Value
 

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" wrote in message
...
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-

Hide or Unhide Column Based on Cell Value
 

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" 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" wrote in message
...
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-