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

Try this

Dim clearscnt As Range

With Worksheets("clears")
Set clearscnt =
Union(.Range("a1:c200").SpecialCells(xlCellTypeFor mulas), _
.Range("a1:c200").SpecialCells(xlCellTypeConstants ))

Worksheets("codes").Range("d2").Value = Intersect(clearscnt.EntireRow, _
.Range("a1:a200")).Count
End With

HTH

Jim Thomlinson


" 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

jim thanks for that although i am getting an error message saying that
no cells were found

tammy

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


If you use the following instead, it will give you the count of
non-blank cells within the range that have data in them
Worksheets("codes").Range("d2").Value = clearscnt.Count.

Counting the number of rows depends on whether the data is in contiguous
cells in the range, i.e., without blanks in the area in which the data
does appear; you didn't provide that information.

Alan Beban

The previous code assumed that there would be something in range A1:C200.
Here is some code that does not make that assumption.

Dim clearscnt As Range

With Worksheets("clears")
On Error Resume Next
Set clearscnt =
Union(.Range("a1:c200").SpecialCells(xlCellTypeFor mulas), _
.Range("a1:c200").SpecialCells(xlCellTypeConstants ))
On Error GoTo 0
If clearscnt Is Nothing Then
Worksheets("codes").Range("d2").Value
Else
Worksheets("codes").Range("d2").Value =
Intersect(clearscnt.EntireRow, _
.Range("a1:a200")).Count
End If
End With

HTH
Jim Thomlinson


" wrote:

jim thanks for that although i am getting an error message saying that
no cells were found

tammy

alan
the data is continuous, However i was using
Worksheets("codes").Range("d2"*).Value = clearscnt.Count.
and this just counts all cells within the range

tammy

Missed one thing

Dim clearscnt As Range

With Worksheets("clears")
On Error Resume Next
Set clearscnt =
Union(.Range("a1:c200").SpecialCells(xlCellTypeFor mulas), _
.Range("a1:c200").SpecialCells(xlCellTypeConstants ))
On Error GoTo 0
If clearscnt Is Nothing Then
Worksheets("codes").Range("d2").Value = 0 'Missed 0 in last post
Else
Worksheets("codes").Range("d2").Value =
Intersect(clearscnt.EntireRow, _
.Range("a1:a200")).Count
End If
End With

Sorry...
Jim Thomlinson


"Jim Thomlinson" wrote:

The previous code assumed that there would be something in range A1:C200.
Here is some code that does not make that assumption.

Dim clearscnt As Range

With Worksheets("clears")
On Error Resume Next
Set clearscnt =
Union(.Range("a1:c200").SpecialCells(xlCellTypeFor mulas), _
.Range("a1:c200").SpecialCells(xlCellTypeConstants ))
On Error GoTo 0
If clearscnt Is Nothing Then
Worksheets("codes").Range("d2").Value
Else
Worksheets("codes").Range("d2").Value =
Intersect(clearscnt.EntireRow, _
.Range("a1:a200")).Count
End If
End With

HTH
Jim Thomlinson


" wrote:

jim thanks for that although i am getting an error message saying that
no cells were found

tammy

Jim
sorry i must be doing something wrong it now just returns a value of 0,
even though there is 10 rows of data in the range

tammy

And the data exists in Cells A1 through C200. I am wondering because it works
for me.

Jim Thomlinson


" wrote:

Jim
sorry i must be doing something wrong it now just returns a value of 0,
even though there is 10 rows of data in the range

tammy

wrote:
alan
the data is continuous, However i was using
Worksheets("codes").Range("d2"*).Value = clearscnt.Count.
and this just counts all cells within the range

tammy

Yes; I mispasted. Meant to say

Worksheets("codes").Range("d2"*).Value = Application.CountA(clearscnt)
for the number of non-blank cells.

Sorry,
Alan Beban

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

THANKS ALAN THAT WORKS GREAT!!!

TAMMY

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

actually, picked up from other "you be da man's!".



Jim Thomlinson wrote:

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