Thanks Dave I see the error of my ways... Your code is correct. My code
required both constants and formulas to exist within the range (otherwise my
union would not work). You be the man...

Jim Thomlinson


"Dave Peterson" wrote:

How about:

Option Explicit
Sub testme()

Dim myVal As Long
Dim RngF As Range 'formulas
Dim RngV As Range 'values
Dim rngB As Range 'both formulas/values
Dim Rng As Range 'original range

With Worksheets("clears")
Set Rng = .Range("a1:C200")
Set RngF = Nothing
Set RngV = Nothing
On Error Resume Next
Set RngF = Rng.Cells.SpecialCells(xlCellTypeFormulas)
Set RngV = Rng.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If RngF Is Nothing Then
Set rngB = RngV
ElseIf RngV Is Nothing Then
Set rngB = RngF
Else
Set rngB = Union(RngF, RngV)
End If

If rngB Is Nothing Then
myVal = 0
Else
myVal = Intersect(.Columns(1), rngB.EntireRow).Cells.Count
End If
End With

Worksheets("codes").Range("d2").Value = myVal

End Sub


wrote:

hi,

i want to count the number of rows within a range with data in , the
range is set at 200 rows but there is a different no of rows with data
in within that range at any one time, iam using the following at the
moemt that just adds all cells within the range

Dim clearscnt As Range
Set clearscnt = Worksheets("clears").Range("a1:c200")
Worksheets("codes").Range("d2").Value = clearscnt.Count

i wish to try and avoid activating any of the cells in the range

tammy

--

Dave Peterson