|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 21st, 2009, 07:32 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=counta() unless row is hidden
hi all
in column A i have some text data. some of the rows have been hidden. i want to count how many are left (visible). but any COUNT functions count the hidden rows, too. any ideas? thanks a lot in advance. susan 
|
|
#2
August 21st, 2009, 07:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=counta() unless row is hidden
Check out the helo for the below function...The below will return the number
of cells with data in Column B which are visible...Please note that hidden cells are hidden through menu... =SUBTOTAL(103,B:B) If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: hi all in column A i have some text data. some of the rows have been hidden. i want to count how many are left (visible). but any COUNT functions count the hidden rows, too. any ideas? thanks a lot in advance. susan
|
|
#3
August 21st, 2009, 08:14 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=counta() unless row is hidden
thanks, jacob, that works great at work on my 2007 excel here at
work. i'll keep it in mind for a good function to use! got any ideas for 2007 versions? when i open the old 2000 version it doesn't work, & at home i only have '03. thanks again susan On Aug 21, 2:43*pm, Jacob Skaria wrote: Check out the helo for the below function...The below will return the number of cells with data in Column B which are visible...Please note that hidden cells are hidden through menu... =SUBTOTAL(103,B:B) If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: hi all in column A i have some text data. *some of the rows have been hidden. *i want to count how many are left (visible). *but any COUNT functions count the hidden rows, too. *any ideas? thanks a lot in advance. susan- Hide quoted text - - Show quoted text -
|
|
#4
August 21st, 2009, 08:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=counta() unless row is hidden
Works for 2003 and greater versions
-- If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: thanks, jacob, that works great at work on my 2007 excel here at work. i'll keep it in mind for a good function to use! got any ideas for 2007 versions? when i open the old 2000 version it doesn't work, & at home i only have '03. thanks again susan On Aug 21, 2:43 pm, Jacob Skaria wrote: Check out the helo for the below function...The below will return the number of cells with data in Column B which are visible...Please note that hidden cells are hidden through menu... =SUBTOTAL(103,B:B) If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: hi all in column A i have some text data. some of the rows have been hidden. i want to count how many are left (visible). but any COUNT functions count the hidden rows, too. any ideas? thanks a lot in advance. susan- Hide quoted text - - Show quoted text -
|
|
#5
August 21st, 2009, 08:42 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=counta() unless row is hidden
ok, thanks!
susan On Aug 21, 3:38*pm, Jacob Skaria wrote: Works for 2003 and greater versions -- If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: thanks, jacob, that works great at work on my 2007 excel here at work. *i'll keep it in mind for a good function to use! *got any ideas for 2007 versions? *when i open the old 2000 version it doesn't work, & at home i only have '03. thanks again susan On Aug 21, 2:43 pm, Jacob Skaria wrote: Check out the helo for the below function...The below will return the number of cells with data in Column B which are visible...Please note that hidden cells are hidden through menu... =SUBTOTAL(103,B:B) If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: hi all in column A i have some text data. *some of the rows have been hidden. *i want to count how many are left (visible). *but any COUNT functions count the hidden rows, too. *any ideas? thanks a lot in advance. susan- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -
|
|
#6
August 22nd, 2009, 02:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=counta() unless row is hidden
Jacob,
Works also if the rows have been hidden via macro, as Range("A2:A6").Rows.EntireRow.Hidden=True will exempt values in rows 2:6 from the subtotal. I just had to check that out (Excel 2003) because I have use for it, and I always forget about the 103 type function feature of SubTotal() which your post reminded me about. Thanks for the reminder. Also works for auto-filtered data, but you'd probably want to subtract 1 from that result to account for the auto-filter label row. Jerry "Jacob Skaria" wrote: Check out the helo for the below function...The below will return the number of cells with data in Column B which are visible...Please note that hidden cells are hidden through menu... =SUBTOTAL(103,B:B) If this post helps click Yes --------------- Jacob Skaria "Susan" wrote: hi all in column A i have some text data. some of the rows have been hidden. i want to count how many are left (visible). but any COUNT functions count the hidden rows, too. any ideas? thanks a lot in advance. susan
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
