Using a function to hide rows
 

First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results of
a given cell. I would use a sub but I need it to update the number of hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the function
is implemented. Any ideas, suggestions, comments on how to fix this? Thanks.

Ryan

Using a function to hide rows
 

Ryan,

Put this code in the Thisworkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target = <Enter the value you are searching for Then
Range(Target.Address).Select
Selection.EntireRow.Hidden = True
End If

"Ryan" wrote:

First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results of
a given cell. I would use a sub but I need it to update the number of hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the function
is implemented. Any ideas, suggestions, comments on how to fix this? Thanks.

Ryan

Using a function to hide rows
 

If you are attempting at using the function from the spreadsheet side, this
would be known as a UDF or User Defined Function. In this case,
performing any such actions is not allowed as UDFs are merely meant to
return a value, not to perform any actions.

On the other hand, if you are calling on this function from another method
(Sub or Function) within VBA, then performing actions is perfectly legal.


Now, I see 3 main issues with your Function below.

First, the whole purpose of a function unlike a subprocedure is to return a
value (via Let statement, which can be omitted and normally is omitted) or a
reference to an object (via Set statement). I therefore ask you, what is it
that this function is suppose to be returning to the caller of this
function?

To have the function return a value, it needs to be using the following
syntax

<FunctionName = <Value

OR

Set <FunctionName = <Object



Now onto the 2nd issue I see. What worksheet are you attempting to hide the
rows on? Is this suppose to hide the rows on the parent worksheet of the
"Input_Cell" range object? If so, try the following statement

Input_Cell.Parent.Rows(InputRow+1).Hidden=True


However, the above statement still isn't valid as the argument for the Rows
property is a read only range object, so we much use the Range object
instead. However, we need to modify the above input into the range object
as the above is still invalid. The input must take on the format of the
following in string format.

<FirstRowNumber":"<LastRowNumber

Such as the following:

CStr(InputRow+1) & ":" & CStr(InputRow+1)

Therefore, your statement will now look like:

Input_Cell.Parent.Range(CStr(InputRow+1) & ":" &
CStr(InputRow+1)).Hidden=True


Main thing you should be able to take from this, don't leave your code to
the possibility of ambiguity, if reasonably possible. I prequalify just
about all of the various variables, methods, objects and anything else
involved. There's only a few things I don't prequalify, such as the data
type conversion functions cause if I attempt to prequalify them functions,
VBA errors out on me. I learned this ambiguity lesson a long while back the
hard way (years), thus why I'm a stickler on this rule. This is also one
such reason why I use "Option Explicit" at the top of every single module in
VBA. This was one of my first lessons learned when I started working with
VBA.

With prequalifying the various things in code, you not only avoid ambiguity,
but you also avoid the priority issues that you may face, if using this code
on multiple computers. Yes, I have faced that issue head on too. Some
systems would allow me to use "Date" variable of the VBA class, but others
wouldn't allow me to use that statement by itself, so I had to modify the
code to be "VBA.Date", so as it would work on all computers.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Ryan" wrote in message
...
First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results
of
a given cell. I would use a sub but I need it to update the number of
hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the
function
is implemented. Any ideas, suggestions, comments on how to fix this?
Thanks.

Ryan

Using a function to hide rows
 

Thanks, that works great but doesn't really accomplish what I'm trying to do.
I don't want the sheet to change every time <Enter the value you are
searching for is inputted, only when the value of a certain cell is changed.
Basically, what I have is a row with validated cell in in and 8 hidden rows
immediately following. The validated cell has a list of values 1-8 and
depending on what value you choose from this list, that many rows below hide
or unhide. Any change you have words on wisdom as how to accomplish this?

Ryan

"JRForm" wrote:

Ryan,

Put this code in the Thisworkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target = <Enter the value you are searching for Then
Range(Target.Address).Select
Selection.EntireRow.Hidden = True
End If

"Ryan" wrote:

First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results of
a given cell. I would use a sub but I need it to update the number of hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the function
is implemented. Any ideas, suggestions, comments on how to fix this? Thanks.

Ryan

Using a function to hide rows
 

Words of wisdom much appreciated. Finally got is working properly thanks to
everyone's help. Thanks again!!

Ryan

"Ronald Dodge" wrote:

If you are attempting at using the function from the spreadsheet side, this
would be known as a UDF or User Defined Function. In this case,
performing any such actions is not allowed as UDFs are merely meant to
return a value, not to perform any actions.

On the other hand, if you are calling on this function from another method
(Sub or Function) within VBA, then performing actions is perfectly legal.


Now, I see 3 main issues with your Function below.

First, the whole purpose of a function unlike a subprocedure is to return a
value (via Let statement, which can be omitted and normally is omitted) or a
reference to an object (via Set statement). I therefore ask you, what is it
that this function is suppose to be returning to the caller of this
function?

To have the function return a value, it needs to be using the following
syntax

<FunctionName = <Value

OR

Set <FunctionName = <Object



Now onto the 2nd issue I see. What worksheet are you attempting to hide the
rows on? Is this suppose to hide the rows on the parent worksheet of the
"Input_Cell" range object? If so, try the following statement

Input_Cell.Parent.Rows(InputRow+1).Hidden=True


However, the above statement still isn't valid as the argument for the Rows
property is a read only range object, so we much use the Range object
instead. However, we need to modify the above input into the range object
as the above is still invalid. The input must take on the format of the
following in string format.

<FirstRowNumber":"<LastRowNumber

Such as the following:

CStr(InputRow+1) & ":" & CStr(InputRow+1)

Therefore, your statement will now look like:

Input_Cell.Parent.Range(CStr(InputRow+1) & ":" &
CStr(InputRow+1)).Hidden=True


Main thing you should be able to take from this, don't leave your code to
the possibility of ambiguity, if reasonably possible. I prequalify just
about all of the various variables, methods, objects and anything else
involved. There's only a few things I don't prequalify, such as the data
type conversion functions cause if I attempt to prequalify them functions,
VBA errors out on me. I learned this ambiguity lesson a long while back the
hard way (years), thus why I'm a stickler on this rule. This is also one
such reason why I use "Option Explicit" at the top of every single module in
VBA. This was one of my first lessons learned when I started working with
VBA.

With prequalifying the various things in code, you not only avoid ambiguity,
but you also avoid the priority issues that you may face, if using this code
on multiple computers. Yes, I have faced that issue head on too. Some
systems would allow me to use "Date" variable of the VBA class, but others
wouldn't allow me to use that statement by itself, so I had to modify the
code to be "VBA.Date", so as it would work on all computers.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Ryan" wrote in message
...
First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results
of
a given cell. I would use a sub but I need it to update the number of
hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the
function
is implemented. Any ideas, suggestions, comments on how to fix this?
Thanks.

Ryan

Using a function to hide rows
 

Ryan,

Let me see if I understand your request. You are searching a column for a
specific value and when/if you find it you want to hide the row. Also you
want to hide the rows that are associated with it. Is this correct?



"Ryan" wrote:

Thanks, that works great but doesn't really accomplish what I'm trying to do.
I don't want the sheet to change every time <Enter the value you are
searching for is inputted, only when the value of a certain cell is changed.
Basically, what I have is a row with validated cell in in and 8 hidden rows
immediately following. The validated cell has a list of values 1-8 and
depending on what value you choose from this list, that many rows below hide
or unhide. Any change you have words on wisdom as how to accomplish this?

Ryan

"JRForm" wrote:

Ryan,

Put this code in the Thisworkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target = <Enter the value you are searching for Then
Range(Target.Address).Select
Selection.EntireRow.Hidden = True
End If

"Ryan" wrote:

First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results of
a given cell. I would use a sub but I need it to update the number of hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the function
is implemented. Any ideas, suggestions, comments on how to fix this? Thanks.

Ryan

Using a function to hide rows
 

Ryan

With DV dropdown in A1 and values of 0 through 8 try this...........

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Rows("2:9").Hidden = True
Rows(1 & ":" & Target.Value + 1).Hidden = False
endit:
Application.EnableEvents = True
End Sub

Select 0 from list and no rows unhide, 1 unhides row 2, 2 unhides 2:3 etc.


Gord Dibben MS Excel MVP

On Thu, 25 Oct 2007 10:46:02 -0700, Ryan wrote:

Thanks, that works great but doesn't really accomplish what I'm trying to do.
I don't want the sheet to change every time <Enter the value you are
searching for is inputted, only when the value of a certain cell is changed.
Basically, what I have is a row with validated cell in in and 8 hidden rows
immediately following. The validated cell has a list of values 1-8 and
depending on what value you choose from this list, that many rows below hide
or unhide. Any change you have words on wisdom as how to accomplish this?

Ryan

"JRForm" wrote:

Ryan,

Put this code in the Thisworkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target = <Enter the value you are searching for Then
Range(Target.Address).Select
Selection.EntireRow.Hidden = True
End If

"Ryan" wrote:

First and foremost, any suggestions would be greatly appreciated!

I'm trying to use a function to hide a row or rows based upon the results of
a given cell. I would use a sub but I need it to update the number of hidden
cells each time the value in the cell changes. So far I've tried something
like:

Function Hide_Rows(Input_Cell as Range)
Dim InputRow = InputCell.Row
If InputCell = (some number) Then
Rows(InputRow+1).Hidden = True
End If....


The function compiles fine, but the row doesn't get hidden when the function
is implemented. Any ideas, suggestions, comments on how to fix this? Thanks.

Ryan