Sub CountNonBlankCells2() 'Returns a count of non-blank
cells in a selection
Dim myCount As Integer 'using the Count ws function
(only counts numbers, no text)

myCount = Application.Count(Selection)

MsgBox "The number of non-blank cell(s) containing numbers is : " &
myCount, vbInformation, "Count Cells"
End Sub

I'd like to have the option of specifying a range but

myCount = Application.Count("A1:A5")

doesn't work, why? What should I do

Thanks

Try,

myCount = Application.Count(Range("A1:A5"))
Which works but I prefer

Set myrange = Range("A1:A5")
myCount = Application.Count(myrange)

Mike

"Simon" wrote:

> Sub CountNonBlankCells2() 'Returns a count of non-blank
> cells in a selection
> Dim myCount As Integer 'using the Count ws function
> (only counts numbers, no text)
>
> myCount = Application.Count(Selection)
>
> MsgBox "The number of non-blank cell(s) containing numbers is : " &
> myCount, vbInformation, "Count Cells"
> End Sub
>
> I'd like to have the option of specifying a range but
>
> myCount = Application.Count("A1:A5")
>
> doesn't work, why? What should I do
>
> Thanks
>

On Aug 1, 11:27*am, Mike H <Mi...@discussions.microsoft.com> wrote:
> Try,
>
> myCount = Application.Count(Range("A1:A5"))
> Which works but I prefer
>
> Set myrange = Range("A1:A5")
> myCount = Application.Count(myrange)
>
> Mike
>
>
>
> "Simon" wrote:
> > Sub CountNonBlankCells2() * * * * * * *'Returns a count of non-blank
> > cells in a selection
> > Dim myCount As Integer * * * * * * * * * *'using the Count ws function
> > (only counts numbers, no text)
>
> > myCount = Application.Count(Selection)
>
> > MsgBox "The number of non-blank cell(s) containing numbers is : " &
> > myCount, vbInformation, "Count Cells"
> > End Sub
>
> > I'd like to have the option of specifying a range but
>
> > myCount = Application.Count("A1:A5")
>
> > doesn't work, why? *What should I do
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Aha, so logical :-D Thanks Mike!