conut rows within a range
 

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

conut rows within a range
 

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

conut rows within a range
 

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

tammy

conut rows within a range
 

conut rows within a range
 

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

conut rows within a range
 

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

conut rows within a range
 

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

conut rows within a range
 

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

conut rows within a range
 

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

conut rows within a range
 

conut rows within a range
 

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

conut rows within a range
 

THANKS ALAN THAT WORKS GREAT!!!

TAMMY

conut rows within a range
 

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

conut rows within a range
 

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