I have been muddling around trying to create a function that simply hides
(or unhides) 'its' row if another cells contents are greater than zero.
just need to know if it can be done, and, what might it look like.
Any help on same would be much appreciated
Thanks
David

Try using a macro rather than a UDF.
--
Gary''s Student


"David Henderson" wrote:

I have been muddling around trying to create a function that simply hides
(or unhides) 'its' row if another cells contents are greater than zero.
just need to know if it can be done, and, what might it look like.
Any help on same would be much appreciated
Thanks
David

Hi,
As n example this hides even rows:

Sub HideRow()
For r = 1 To 5
If Cells(r, 1) Mod 2 = 0 Then Rows(r).EntireRow.Hidden = True
Next r
End Sub


HTH
"David Henderson" wrote:

I have been muddling around trying to create a function that simply hides
(or unhides) 'its' row if another cells contents are greater than zero.
just need to know if it can be done, and, what might it look like.
Any help on same would be much appreciated
Thanks
David

many thanks
all the best
David
"Toppers" wrote in message
...
Hi,
As n example this hides even rows:

Sub HideRow()
For r = 1 To 5
If Cells(r, 1) Mod 2 = 0 Then Rows(r).EntireRow.Hidden = True
Next r
End Sub


HTH
"David Henderson" wrote:

I have been muddling around trying to create a function that simply hides
(or unhides) 'its' row if another cells contents are greater than zero.
just need to know if it can be done, and, what might it look like.
Any help on same would be much appreciated
Thanks
David

David,

The fact is, a UDF used in a worksheet cell is not permitted to change the
excel environment. this includes such things and changing formats, changing
the values of other cells or as in your case, hiding or unhiding rows or
columns. Even if you wrote a sub and called it from your UDF, this would
not work. What you can do is use the Worksheet_Calculate event to perform
this function

Right click on the sheet tab where you want this behavior and select View
code. In the left dropdown at the top of the module select Worksheet and in
the right dropdown at the top of the module select Calculate.

Private Sub Worksheet_Calculate()
Dim cell as Range
for each cell in Range("B2:B50")
if cell.Value < 10 then
cell.EntireRow.Hidden = True
else
cell.EntireRow.Hidden = False
end if
Next
End Sub

then in the cells in column B, you could have your formula (or UDF) to
determine whether the row should be hidden or not.

Adjust to suit your requirements.

--
Regards,
Tom Ogilvy

"David Henderson" wrote in message
...
many thanks
all the best
David
"Toppers" wrote in message
...
Hi,
As n example this hides even rows:

Sub HideRow()
For r = 1 To 5
If Cells(r, 1) Mod 2 = 0 Then Rows(r).EntireRow.Hidden = True
Next r
End Sub


HTH
"David Henderson" wrote:

I have been muddling around trying to create a function that simply
hides
(or unhides) 'its' row if another cells contents are greater than zero.
just need to know if it can be done, and, what might it look like.
Any help on same would be much appreciated
Thanks
David