I am looking for a way to count the values rows that are visible. I have rows
A14:A83 with values. Rows A39:A83 may be hidden at time. When these rows are
hidden is there a way to count the values in rows A14:A38 only, even if rows
A39:A83 still contain values?


--
Randy Street
Rancho Cucamonga, CA

On Fri, 11 Dec 2009 15:53:01 -0800, Randy
<(E-Mail Removed)> wrote:

>I am looking for a way to count the values rows that are visible. I have rows
>A14:A83 with values. Rows A39:A83 may be hidden at time. When these rows are
>hidden is there a way to count the values in rows A14:A38 only, even if rows
>A39:A83 still contain values?


Hidden rows do not show up in a chart, so it *must* be possible.

When you look at a range, you should see values in the status bar at
the bottom of the Excel screen window. That status bar gives a few
statistics. Try looking at the bar, then hiding a row and see if the
value(s) change in the status bar.

OR, you could name the range above the hidden range, name the hidden
range, and name the rows below the hidden range, and perform summing
operations on the named ranges only, leaving out the hidden range by
simply not including it in the calculations.

Give this statement a try...

VisibleValues = Range("A14:A83").SpecialCells(xlCellTypeConstants). _
SpecialCells(xlCellTypeVisible).Count

Note: This code assumes that the values are constants and not values from
formulas.

--
Rick (MVP - Excel)


"Randy" <(E-Mail Removed)> wrote in message
news706538E-7262-4175-B8E6-(E-Mail Removed)...
>I am looking for a way to count the values rows that are visible. I have
>rows
> A14:A83 with values. Rows A39:A83 may be hidden at time. When these rows
> are
> hidden is there a way to count the values in rows A14:A38 only, even if
> rows
> A39:A83 still contain values?
>
>
> --
> Randy Street
> Rancho Cucamonga, CA

Hi Randy

The way I understand it you want to put a sum at the bottom of your
used range to sum all of the values which are not hidden. Microsoft
have a great custom function to do this, see below. Place in a normal
module.

Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function

At the bottom of your used range use this formula.

=Sum_Visible_Cells(A14:A83)

If any cells are hidden inbetween these stated rows they will be
excluded from the count.

Take care

Marcus

Hi Randy

If you are looking for a worksheet funciton then try the below formula which
will count only the cells with values which are visible

=SUBTOTAL(102,A14:A83)

--
Jacob


"Randy" wrote:

> I am looking for a way to count the values rows that are visible. I have rows
> A14:A83 with values. Rows A39:A83 may be hidden at time. When these rows are
> hidden is there a way to count the values in rows A14:A38 only, even if rows
> A39:A83 still contain values?
>
>
> --
> Randy Street
> Rancho Cucamonga, CA

Take a look at this:
http://www.ozgrid.com/Excel/excel-subtotal-function.htm


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

> Hi Randy
>
> If you are looking for a worksheet funciton then try the below formula which
> will count only the cells with values which are visible
>
> =SUBTOTAL(102,A14:A83)
>
> --
> Jacob
>
>
> "Randy" wrote:
>
> > I am looking for a way to count the values rows that are visible. I have rows
> > A14:A83 with values. Rows A39:A83 may be hidden at time. When these rows are
> > hidden is there a way to count the values in rows A14:A38 only, even if rows
> > A39:A83 still contain values?
> >
> >
> > --
> > Randy Street
> > Rancho Cucamonga, CA

On Sat, 12 Dec 2009 09:20:02 -0800, ryguy7272
<(E-Mail Removed)> wrote:

>''Yes''