row number of last non-blank cell

[Bill Brehm]

I've tried a bunch of ideas but no luck yet.

I used to use COUNTA() to find the number of cells in a column so I could
create an array reference to the range of cells with values using OFFSET().
Now my column may have some blank entries. COUNTA() still returns the number
of non-blank cells so my range array falls short by the number of blank
cells.

How can I find the row number of the last cell that is not blank. I need
this in a worksheet function, not a macro or not selecting with menu
commands.

Thanks...

 

[T. Valko]

What type of data is in the range? Is it text, numeric, or could it be both?
Are there any formulas in the range that return formula blanks ("") ?

 

[Bill Brehm]

In this case there is text cells or blank cells only.

However I (or others) might benefit from knowing how to solve for the other
cases too. I have had problems with subsequent processing of cells that use
a formula like =IF(A1>0,A1,"").

Thanks...

 

[Rick Rothstein]

The following array-entered** formula will find the row number of the last non-blank cell in Column A, no matter if the cells contain text, numbers or both (make the obvious change to the ranges for a different column)...

=MAX(ROW(A$1:A$65535)*(A$1:A$65535<>""))

**Commit this formula using Ctrl+Shift+Enter, *not* just Enter by itself.

 

[Bill Brehm]

This worked. Thank you.

Sadly, it doesn't work as =MAX(ROW(A:A)*(A:A<>"")); i get #NUM! instead.

The following array-entered** formula will find the row number of the last
non-blank cell in Column A, no matter if the cells contain text, numbers or
both (make the obvious change to the ranges for a different column)...

=MAX(ROW(A$1:A$65535)*(A$1:A$65535<>""))

**Commit this formula using Ctrl+Shift+Enter, *not* just Enter by itself.

 

[Rick Rothstein]

That's a limitation of XL2003 and lower... you can use it in XL2007 (and I'm guessing XL2010 as well).

 

[Ms-Exl-Learner]

The below formula will get you the row number of the last nonblank cell.
Here the range is marked to Column A.

=SUMPRODUCT(MAX((ROW(A1:A65535))*(A1:A65535<>"")))+(A65536<>"")

Change the Column A to your desired Column No, if requred.

If you are using Excel 2003 then dont refer the cell range as A:A like that
mention it as cell reference (Like A1:A65535).

In excel 2007 you can refer the range as A:A or B:B like that, no issues.

Remember to Click Yes, if this post helps!

 

[T. Valko]

If you're wanting to build a dynamic range...

While OFFSET will work, it's also the pedantic method. Think outside the
box!

For ranges with TEXT only (could contain empty cells):

=$A$2:INDEX($A$2:$A$100,MATCH("zzzzzzzzzz",$A$2:$A$100))

For ranges that contain NUMBERS only (could contain empty cells):

=$A$2:INDEX($A$2:$A$100,MATCH(1E100,$A$2:$A$100))

For ranges that contain both TEXT and NUMBERS (could contain empty cells):

=$A$2:INDEX($A$2:$A$100,LOOKUP(2,1/($A$2:$A$100<>""),ROW($A$2:$A$100))-ROW($A$2)+1)

In each case, adjust for a reasonable end of range A100.

 

[T. Valko]

=MAX(ROW(A:A)*(A:A<>""))

If you don't have that much data that is terribly inefficient. Most people
use A:A because it's a lot easier than typing A1:A10000 and it looks cleaner
just using A:A. However, if you're using Excel 2007 then every one of the 1
million+ cells in column A will be evaluated. If your actual range only goes
to row 10k then you're wasting processing power by having to evaluate over 1
million empty cells.

--
Biff
Microsoft Excel MVP


That's a limitation of XL2003 and lower... you can use it in XL2007 (and I'm
guessing XL2010 as well).

 

[Bill Brehm]

I'm using Excel 2003, but it's good to know that 2007 supports a lot more
rows.

I also like using A:A when possible, because when one uses A1:Annn and
eventually adds data in rows nnn+1, etc., the formula fails to work
correctly and probably without warning.

 

[Bill Brehm]

Pedantic? I'll admit, I had to look the word up. I think that and admitting
it is proof enough I wasn't being pedantic or (according to the dictionary)
pretentious or ostentatious.

I use OFFSET because it works, not to show off. I don't even bother to
remember what all the Excel functions do. I know they exist and I look them
up when I need them.

Do any of your alternatives have any advantages over OFFSET (snide comment
that I was thinking of putting here withheld)?

 

[Rick Rothstein]

See inline comments...

Pedantic? I'll admit, I had to look the word up. I think that and admitting
it is proof enough I wasn't being pedantic or (according to the dictionary)
pretentious or ostentatious.

I use OFFSET because it works, not to show off. I don't even bother to
remember what all the Excel functions do. I know they exist and I look them
up when I need them.

The definition I have for "pedantic" is this...

"too concerned with what are thought to be correct rules and details"

and in that context, I think Biff was just saying "don't use OFFSET just because everyone else uses OFFSET".

Do any of your alternatives have any advantages over OFFSET (snide comment
that I was thinking of putting here withheld)?

The OFFSET function is Volatile while the INDEX, MATCH, ROW and LOOKUP functions Biff uses are not. Here is a good site to learn all about Volatile functions...

http://www.decisionmodels.com/calcsecretsi.htm

 

[T. Valko]

I was not not being critical!

Rick interpreted the meaning of my comment exactly:

I think Biff was just saying "don't use OFFSET
just because everyone else uses OFFSET".

Good luck!

 

[T. Valko]

Some functions will only evaluate the used range while array formulas will
evaluate every cell referenced whether they're within the used range or not.

For expansion, you may be able to anticipate how much data you'll end up
with. For example, if you add a new entry for daily record keeping then you
can anticipate that for the year you will probably need at least 365 rows of
area. So you can set references to row 370 and know that will be within your
range for expansion.

For tips on efficiency see the link Rick posted in his reply.

 

[Bill Brehm]

Biff,

The definition I thought applied here was: Being showy of one's knowledge,
often in a boring manner.

I don't come onto these types of forums very often but when I do there is
often someone that tends to be insulting with their replies. I guess I
thought you were doing that.

My apologies if I jumped to the wrong conclusion and acted defensively.

Bill