Re: Count with Hidden cells

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I think you'll need a userdefined function.

Option Explicit
Function myCount(rng As Range)
Application.Volatile True

Dim myCell As Range
Dim myCtr As Long

myCtr = 0
For Each myCell In rng.Cells
If IsEmpty(myCell) _
Or myCell.EntireRow.Hidden = True _
Or myCell.EntireColumn.Hidden = True Then
'do nothing, ignore it
Else
myCtr = myCtr + 1
End If
Next myCell

myCount = myCtr

End Function

======
In xl2002, I don't think that hiding rows causes a recalculation, though. You
may want to hit F9 before you trust the result of the UDF. (xl2003 recalcs when
you hide a row--but you don't need this kind of function in xl2003--as Peo
said.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=mycount(a2:a33)



Stretch wrote:
>
> Also, I have 2002 instead of 2003. Any ideas on how it works for that?
>
> "Peo Sjoblom" wrote:
>
> > Depends on the version of excel, excel 2003 works like
> >
> > =SUBTOTAL(103,A1:A10)
> >
> > won't count hidden cells
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Stretch" <Stretch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:9F387E47-FC1F-4AEB-8A6C-2B37BC012DD1@xxxxxxxxxxxxxxxx
> > > Is there a way to count the number of text filled cells excluding the
> > > cells
> > > that I have hidden?
> >
> >

--

Dave Peterson
.

Relevant Pages

  • Re: Autofit merged cells with wrapped text
    ... You could select your cells that you want fixed and then run the macro against ... Sub doTheSelection() ... Dim myCell As Range ...
    (microsoft.public.excel.programming)
  • Re: VBA to lock column values
    ... Dim myCell As Range ... have column headers of specific dates. ... dates interjected between monthly dates in the cells in the row that serves ...
    (microsoft.public.excel.programming)
  • Re: Selecting all the active cells in a named column.
    ... Sub testme() ... Dim myCell As Range ... I need to select all the cells in a named column. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Lock range based on conditional format colour.
    ... Dim myCell As Range ... For Each myCell In Selection ... way to lock cells depending on their format. ...
    (microsoft.public.excel.misc)
  • Re: re-naming styles
    ... Then after all the cells on all the sheets ... Dim myCell As Range ... Set TestStyle = myWorkbook.Styles ...
    (microsoft.public.excel.programming)