Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
On Fri, 11 Dec 2009 15:53:01 -0800, 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? 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count values in Visible ROWS only
On Sat, 12 Dec 2009 09:20:02 -0800, ryguy7272
wrote: ''Yes'' |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count identical values in 2 rows | Excel Worksheet Functions | |||
Need a formula to count values in different rows | Excel Worksheet Functions | |||
VBA (or Excel) crashes if Count of Visible Rows in Autofilter Range = 1 | Excel Programming | |||
Function to display a count of visible cells/rows... doesn't | Excel Programming | |||
count only the visible rows in a data table | Excel Programming |