Is there a function to show if cell/row(X:Y) is hidden? or width?

[Guest]

I am looking for an Excel function that will return info about whether a row
is hidden or not... returning the height may work too, if height value goes to
0 or low value when the row is hidden.

I am wanting to change a formula in Excel, depending on whether a cell/row is
hidden or not. There IS a function =CELL("format",E20) that will give the
formatting of a dell's data, or =CELL("width",B12) that will show width; but
I can NOTfind something similar to =CELL("width",G47)

One of my intended uses:
Conditional formatting, to set highlight color of every other line of a
report:
I can set up conditional formatting with =EVEN(ROW(F14)) that works FINE to
allow formatting to highlight every other row, adjusting properly when new
rows
are added in the middle of a table... BUT when you use a FILTER on the data
rows
and some of them are hidden, it looks bad, when the DISPLAYED rows have
scattered background coloring. Instead of using ROW(F14), I'm wanting to
calculate my own value for "displayed row" with something like this:
=IF(CELL("height",G22)>3, G21+1, G21) which would increment the displayed
row counter ONLY if the height of G22 was more than 3 pixels.

OK, that's enough detail for now- I think you get the idea.

Perhaps there

 

[Biff]

Hi!

This will return a rows height**:

Create this named formula:
Goto Insert>Name>Define
Name: RowHeight
Refers to: =GET.CELL(17,INDIRECT("A1"))
OK

Formula to get the row height of row 1 (that's what the reference to A1
means in the above formula. It will ALWAYS point to cell A1)

=RowHeight

** Simply hidding a row/column does not trigger a calculation so the formula
will not update until you either manually calculate or an automatic
calculation is triggered by some other event. However, applying a filter
that hides rows DOES trigger a calculation. Both hidden and filtered rows
will return a row height of zero.

Biff

 

[Guest]

Thanks... however this sort of named function doesn't let me pass any
parameters (like the number of the row I want to check).
.... for instance =ROWHEIGHT(6)

It might help if I was more fluent in VBA.
However, I DID find a solution: I was needing to only SUM the un-hidden(or
filtered)
rows of data, and SUM(range) continues to add ALL rows, whether hidden or not.
That led me to the SUBTOTAL(type,range) function, which allows formulas to
ONLY
include visible rows in the calculation.

** GOOD FIND **

From that, I could create a running COUNT of just the visible rows, and write
the following formula for conditional formatting...

=MOD(SUBTOTAL(103,$A$10:$A11)+1,2)

This will return TRUE for every other visible row, starting at row ten (top
of table).
Note: 103 is a numeric code for COUNTA() in SUBTOTAL. I am effectively
counting
the number of rows that exist below row 10, and then MOD-ing.

I also decided that I wanted to stop formatting at the bottom of my table of
data.
Because of this, I changed the formula to NOT format rows that didn't have any
data in the first column of the row (no row heading) This resulted in:

=AND(MOD(SUBTOTAL(103,$A$10:$A11)+1,2),$A11<>"")


It's a different solution, but thanks for the help.

Joseph

 

[Guest]

OK,

After "tweaking around" for several hours, I found another way.
I learned how to pass a parameter into a VBA function, and
use the attribute of a Range called Rowheight

The tiny little VBA function is below:

Function CellHeight(Cell_Address As Range) As Variant
CellHeight = Cell_Address.RowHeight
End Function

As mentioned above, it doesn't recalculate when I resize a row,
but it does update when any cell is modified/typed in/deleted.