udf to hide a row

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> many thanks
> all the best
> David
> "Toppers" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
> >>
>
>