Hide or Unhide Column Based on Cell Value

[ChrisR]

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-

 

[windsurferLA]

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

 

[STEVE BELL]

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...

 

[ChrisR]

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-