Hide or Unhide Column Based on Cell Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisR - 30 Mar 2006 22:46 GMT

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-

Reply to this Message

windsurferLA - 31 Mar 2006 18:11 GMT

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

> 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
[quoted text clipped - 28 lines]
>
> c-

Reply to this Message

STEVE BELL - 31 Mar 2006 20:53 GMT

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

Signature

steveB

Remove "AYN" from email to respond

> Two issues:
>
[quoted text clipped - 49 lines]
>>
>> c-

Reply to this Message

ChrisR - 31 Mar 2006 23:06 GMT

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-

> Chris,
>
[quoted text clipped - 65 lines]
> >>
> >> c-

Reply to this Message