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...

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 ("") ?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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 ("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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 (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message ...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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 ("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

This worked. Thank you.

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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 ("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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

--
Rick (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message ...
This worked. Thank you.

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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 ("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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!

--------------------
(Ms-Exl-Learner)
--------------------


"Bill Brehm" wrote:

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...


.

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.

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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 ("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

=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


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

--
Rick (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
This worked. Thank you.

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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
("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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.


"T. Valko" wrote in message
...
=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


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

--
Rick (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
This worked. Thank you.

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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
("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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)?


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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
("") ?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...

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


--
Rick (MVP - Excel)

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.

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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.


"T. Valko" wrote in message
...
=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


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

--
Rick (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
This worked. Thank you.

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

"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Bill Brehm" <don't want spam wrote in message
...
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(A10,A1,"").

Thanks...

"T. Valko" wrote in message
...
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
("")
?

--
Biff
Microsoft Excel MVP


"Bill Brehm" <don't want spam wrote in message
...
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...