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" <(E-Mail Removed)> wrote in message
news:29138BD3-FECC-4FA6-A428-(E-Mail Removed)...
> 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
>>
>>
>>