Adjustment to count cells (in range code)

[Simon]

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

 

[Mike H]

Try,

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

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

Mike

 

[Simon]

Try,

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

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

Mike











- Show quoted text -

Aha, so logical :-D Thanks Mike!