Function to hide row

[Excelgoeroe]

I am looking for a UDF that will hide a row on a condition. So not in a macro that needs an event, but in a formula:

=IF(A8=0;HideRow()) just as =IF(A8=0;Now())

I tried the following Function

Function RowHide(Arg1 As Integer) As Action


If Arg1 = 0 Then
ActiveSheet.Select
ActiveCell.Select
Selection.EntireRow.Hidden = True
End If

End Function

 

[circledchicken]

Hi,

I don't think you will be able to do that. See here for an introduction to UDF's:
Writing Your Own Functions In VBA

and note the following statement in the article:

A UDF can only return a value to the cell(s) whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell.

 

[Excelgoeroe]

Hi thanks for your comment. You are completely right, this is what's happening.
Now, I am just curious how I can solve the basic wants to have a function to conditionally hide a row or column without a macro.
I have tried to use conditional formatting, but no luck so far.
Has anybody a suggestion?

 

[Smitty]

Welcome to the Board!

Same as with UDF's, Conditional Formatting can't perform physical functions like hiding rows. But you can try AutoFilter, which is a lot more efficient than trying to select/loop through rows anyway.

HTH,

 

[Excelgoeroe]

That is a good hint. Never thought of it for this purpose but it will be a solution.
Thanks