software

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(quot;formatquot;,E20) that will give the
formatting of a dell's data, or =CELL(quot;widthquot;,B12) that will show width; but
I can NOTfind something similar to =CELL(quot;widthquot;,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 quot;displayed rowquot; with something like this:
=IF(CELL(quot;heightquot;,G22)gt;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 thereHi!

This will return a rows height**:

Create this named formula:
Goto Insertgt;Namegt;Define
Name: RowHeight
Refers to: =GET.CELL(17,INDIRECT(quot;A1quot;))
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

quot;Joseph in Atlantaquot; lt;Joseph in gt; wrote in
message news
gt;I am looking for an Excel function that will return info about whether a
gt;row
gt; is hidden or not... returning the height may work too, if height value
gt; goes to
gt; 0 or low value when the row is hidden.
gt;
gt; I am wanting to change a formula in Excel, depending on whether a cell/row
gt; is
gt; hidden or not. There IS a function =CELL(quot;formatquot;,E20) that will give the
gt; formatting of a dell's data, or =CELL(quot;widthquot;,B12) that will show width;
gt; but
gt; I can NOTfind something similar to =CELL(quot;widthquot;,G47)
gt;
gt; One of my intended uses:
gt; Conditional formatting, to set highlight color of every other line of a
gt; report:
gt; I can set up conditional formatting with =EVEN(ROW(F14)) that works FINE
gt; to
gt; allow formatting to highlight every other row, adjusting properly when new
gt; rows
gt; are added in the middle of a table... BUT when you use a FILTER on the
gt; data
gt; rows
gt; and some of them are hidden, it looks bad, when the DISPLAYED rows have
gt; scattered background coloring. Instead of using ROW(F14), I'm wanting to
gt; calculate my own value for quot;displayed rowquot; with something like this:
gt; =IF(CELL(quot;heightquot;,G22)gt;3, G21 1, G21) which would increment the displayed
gt; row counter ONLY if the height of G22 was more than 3 pixels.
gt;
gt; OK, that's enough detail for now- I think you get the idea.
gt;
gt; Perhaps there
gt;
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),$A11lt;gt;quot;quot;)It's a different solution, but thanks for the help.

Joseph
quot;Biffquot; wrote:

gt; Hi!
gt;
gt; This will return a rows height**:
gt;
gt; Create this named formula:
gt; Goto Insertgt;Namegt;Define
gt; Name: RowHeight
gt; Refers to: =GET.CELL(17,INDIRECT(quot;A1quot;))
gt; OK
gt;
gt; Formula to get the row height of row 1 (that's what the reference to A1
gt; means in the above formula. It will ALWAYS point to cell A1)
gt;
gt; =RowHeight
gt;
gt; ** Simply hidding a row/column does not trigger a calculation so the formula
gt; will not update until you either manually calculate or an automatic
gt; calculation is triggered by some other event. However, applying a filter
gt; that hides rows DOES trigger a calculation. Both hidden and filtered rows
gt; will return a row height of zero.
gt;
gt; Biff
gt;
gt; quot;Joseph in Atlantaquot; lt;Joseph in gt; wrote in
gt; message news
gt; gt;I am looking for an Excel function that will return info about whether a
gt; gt;row
gt; gt; is hidden or not... returning the height may work too, if height value
gt; gt; goes to
gt; gt; 0 or low value when the row is hidden.
gt; gt;
gt; gt; I am wanting to change a formula in Excel, depending on whether a cell/row
gt; gt; is
gt; gt; hidden or not. There IS a function =CELL(quot;formatquot;,E20) that will give the
gt; gt; formatting of a dell's data, or =CELL(quot;widthquot;,B12) that will show width;
gt; gt; but
gt; gt; I can NOTfind something similar to =CELL(quot;widthquot;,G47)
gt; gt;
gt; gt; One of my intended uses:
gt; gt; Conditional formatting, to set highlight color of every other line of a
gt; gt; report:
gt; gt; I can set up conditional formatting with =EVEN(ROW(F14)) that works FINE
gt; gt; to
gt; gt; allow formatting to highlight every other row, adjusting properly when new
gt; gt; rows
gt; gt; are added in the middle of a table... BUT when you use a FILTER on the
gt; gt; data
gt; gt; rows
gt; gt; and some of them are hidden, it looks bad, when the DISPLAYED rows have
gt; gt; scattered background coloring. Instead of using ROW(F14), I'm wanting to
gt; gt; calculate my own value for quot;displayed rowquot; with something like this:
gt; gt; =IF(CELL(quot;heightquot;,G22)gt;3, G21 1, G21) which would increment the displayed
gt; gt; row counter ONLY if the height of G22 was more than 3 pixels.
gt; gt;
gt; gt; OK, that's enough detail for now- I think you get the idea.
gt; gt;
gt;
gt;
gt;

OK,

After quot;tweaking aroundquot; 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.
quot;Joseph in Atlantaquot; wrote:

gt; Thanks... however this sort of named function doesn't let me pass any
gt; parameters (like the number of the row I want to check).
gt; ... for instance =ROWHEIGHT(6)
gt;
gt; It might help if I was more fluent in VBA.