This finds the last row in the column with anything in it
r=cells(rows.Count,"a").End(xlUp).Row
But, if that cell has a formula in it that returns "", then the statement
returns that row because there is a formula in it. I know I could follow
the statement with something clumsy like
While cells(r,"a")="" r=r-1
But I wonder if there isn't a simpler way. Thanks, James

will only work if the blank cells are all at the bottom of the range...

r = Cells(Rows.Count, "a").End(xlUp).Row
lBlanks = WorksheetFunction.CountBlank(Range("A1:A" & r))
r = r - lBlanks



--
Hope that helps.

Vergel Adriano


"Zone" wrote:

This finds the last row in the column with anything in it
r=cells(rows.Count,"a").End(xlUp).Row
But, if that cell has a formula in it that returns "", then the statement
returns that row because there is a formula in it. I know I could follow
the statement with something clumsy like
While cells(r,"a")="" r=r-1
But I wonder if there isn't a simpler way. Thanks, James

Thanks, Vergel.
"Vergel Adriano" wrote in message
...
will only work if the blank cells are all at the bottom of the range...

r = Cells(Rows.Count, "a").End(xlUp).Row
lBlanks = WorksheetFunction.CountBlank(Range("A1:A" & r))
r = r - lBlanks



--
Hope that helps.

Vergel Adriano


"Zone" wrote:

This finds the last row in the column with anything in it
r=cells(rows.Count,"a").End(xlUp).Row
But, if that cell has a formula in it that returns "", then the statement
returns that row because there is a formula in it. I know I could follow
the statement with something clumsy like
While cells(r,"a")="" r=r-1
But I wonder if there isn't a simpler way. Thanks, James