Bottom Cell with VALUE
 

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

Bottom Cell with VALUE
 

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

Bottom Cell with VALUE
 

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