Row visible by filter

[Arien]

I would like to make a boolean on every row in a list that tells me if that row is hidden by a filter or not.
(Want to use that for several sumproduct formula's)

Can that be done a formula or would I need VBA?

Thanks for your help.
Arien

[zbor]

How you see it with boolean if it's hidden?

[Arien]

I do not see it. But I can calculate with it...


I tried something like

Function is_hidden(this_cel) As Boolean
is_hidden = this_cel.Hidden
End Function

But that that is just not correct.

Thanks.
Arien

[shg]

Try this:

Function IsHidden() As Boolean
    Application.Volatile
    IsHidden = Application.Caller.EntireRow.Hidden
End Function

Example usage: =IF(IsHidden(), 1, 0)

[Arien]

Thanks shg.
Please allow me to practice with it as it seems to slow down my computer pretty much.
Also I need to press F9 after entering the formula. Then it only calculates 1 cell, while the other cells change to #value

regards,
Arien

[shg]

I don't understand the problem you're having. It should be entered as an ordinary formula in a cell. Post a workbook?

The Application.Volatile causes the function to calculate when anything changes. It might be changed to reduce the impact.