Count values in Visible ROWS only

[Randy]

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?

 

[CellShocked]

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.

 

[Rick Rothstein]

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.

 

[marcus]

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

 

[Jacob Skaria]

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)

 

[ryguy7272]

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

 

[Archimedes' Lever]

''Yes''