Bottom Cell with VALUE

  • Thread starter Thread starter Zone
  • Start date Start date
Z

Zone

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
 
Thanks, Vergel.
Vergel Adriano said:
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Array with many cells from a row but not in order or together 11
vlookup issue 2
Fill in the blanks 1
ClearContents if #VALUE! found in cell 2
Hidden sheet bleeds through 3
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Executing Part Of Macro Only On Cells Meeting A Condition 2
i = row number of last occurance of duplicate string Column A 4

Back
Top