Last "Numeric Value" in a column of formulas

[iamnu]

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

 

[iamnu]

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

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

 

[Bob Phillips]

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

--
---
HTH

Bob


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

 

[Sandy Mann]

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.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[iamnu]

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.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

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

 

[Bob Phillips]

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

--
---
HTH

Bob


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

 

[Ron Rosenfeld]

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.

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

 

[Sandy Mann]

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.

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk