Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to hide/unhide WS based upon WS tab color | Excel Discussion (Misc queries) | |||
Hide or unhide sheets based on cell | Excel Discussion (Misc queries) | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) | |||
Hide/Unhide column based on cell value | Excel Programming | |||
Hide/unhide sheet macro based on cell calculation | Excel Programming |