ExcelBanter - View Single Post

Alan Beban[_2_]

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