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

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

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)

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


.