Last "Numeric Value" in a column of formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

iamnu - 20 Mar 2008 20:14 GMT

I have formulas in column A.
I will sometimes enter a numeric value in one of the rows of column A.

How do I find the last Row in which I have entered a numeric value?

Example Column A (below) should produce a result of Row 10 because 7
is the last numeric value:
=B1
=B2
2
7
=B5
=B6
=B7
2
9
3
=B11
=B12
=B13

Thanks for your help...

Reply to this Message

iamnu - 20 Mar 2008 20:17 GMT

> I have formulas in column A.
> I will sometimes enter a numeric value in one of the rows of column A.
[quoted text clipped - 18 lines]
>
> Thanks for your help...

Whoops!
Example Column A (below) should produce a result of Row 10 because 3
is the last numeric value:
Sorry about that...

Reply to this Message

Sandy Mann - 20 Mar 2008 22:15 GMT

I have half an answer for you. The Macro:

Sub LastCell()
For Each cell In Range("A:A").SpecialCells(xlCellTypeConstants, 23)
Last = cell.Row
Next cell
Range("C1").Value = Last
End Sub

returns your required 10 in C1 but if I turn it into a Function as in:

Function LastCell()
For Each cell In Range("A:A").SpecialCells(xlCellTypeConstants, 23)
Last = cell.Row
Next cell
LastCell = Last
End Function

it returns 65536. In other words the SpecialCells is not being picked up in
the Finction. Perhaps one of the experts can tell us both why.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>> I have formulas in column A.
>> I will sometimes enter a numeric value in one of the rows of column A.
[quoted text clipped - 23 lines]
> is the last numeric value:
> Sorry about that...

Reply to this Message

iamnu - 20 Mar 2008 22:38 GMT

> I have half an answer for you. The Macro:
>
[quoted text clipped - 54 lines]
> > is the last numeric value:
> > Sorry about that...

Thank You Mr. Phillips!

I changed your formula as follows to check for the number.
=MAX(IF(A1:A1000>0,ROW(A1:A1000)))

It works great.

Now maybe you could explain WHEN one should use an "array formula"?

Thanks again...

Reply to this Message

Bob Phillips - 20 Mar 2008 23:03 GMT

When passing an array to a function that 'normally' takes a single cell.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> I have half an answer for you. The Macro:
>>
[quoted text clipped - 66 lines]
>
> Thanks again...

Reply to this Message

Ron Rosenfeld - 20 Mar 2008 23:33 GMT

>Function LastCell()
> For Each cell In Range("A:A").SpecialCells(xlCellTypeConstants, 23)
[quoted text clipped - 5 lines]
>it returns 65536. In other words the SpecialCells is not being picked up in
>the Finction. Perhaps one of the experts can tell us both why.

I can't tell you why, but it happens in Excel 2007 also.

Even simpler, try this:

Function constants()
Debug.Print Range("A:A").SpecialCells(xlCellTypeConstants).Address
End Function

vs

Sub constants()
Debug.Print Range("A:A").SpecialCells(xlCellTypeConstants).Address
End Sub

The first prints $A:$A

The second the more limited range where I happen to have constants
$A$1:$A$17

I'm not sure when he posted this, but J Walkenback found a bug in the
SpecialCells method having to do with the range size. But that does not seem
to be the case here. http://www.j-walk.com/ss/excel/odd/odd29.htm

Hopefully, someone has more insight into this issue.
--ron

Reply to this Message

Sandy Mann - 21 Mar 2008 00:30 GMT

Thank you very much for your answer Ron. Yes I had already found that I
could return the limited range of constants only - at least in a Macro. I
didn't find the same limit that Bob Umlas reported in the link that you
supplied, perhaps the bug differs with different Special Cell Types.

As it has transpired from the OP's reply to Bob Philips, it seems that I was
misreading the post anyway. I thought that the OP wanted the Row last of
manually entered data even when the formulas were returning values other
than zero.

If someone else has any insight into the SpecialCells bug then that would be
most interesting but in the mean time I will try not to worry abut it.

Thank you again.

Signature

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> On Thu, 20 Mar 2008 21:15:26 -0000, "Sandy Mann"
> <sandymann2@mailinator.com>
[quoted text clipped - 37 lines]
> Hopefully, someone has more insight into this issue.
> --ron

Reply to this Message

Bob Phillips - 20 Mar 2008 21:07 GMT

=MAX(IF(A1:A1000<>"",ROW(A1:A1000)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have formulas in column A.
> I will sometimes enter a numeric value in one of the rows of column A.
[quoted text clipped - 18 lines]
>
> Thanks for your help...

Reply to this Message

Rate this thread: