Last "Numeric Value" in a column of formulas  
Author Message
Gavon





PostPosted: Thu Mar 20 14:17:47 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas

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

Excel73  
 
 
iamnu





PostPosted: Thu Mar 20 14:17:47 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas
> 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





PostPosted: Thu Mar 20 15:07:21 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas =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)





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


 
 
Sandy





PostPosted: Thu Mar 20 16:15:26 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas 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








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


 
 
iamnu





PostPosted: Thu Mar 20 16:38:29 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas
> 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
>


>

>

>

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

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





PostPosted: Thu Mar 20 17:03:27 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas 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)






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


>>

>>

>>

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


 
 
Ron





PostPosted: Thu Mar 20 17:33:26 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas


>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





PostPosted: Thu Mar 20 18:30:35 CDT 2008 Top

Excel >> Last "Numeric Value" in a column of formulas 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







> On Thu, 20 Mar 2008 21:15:26 -0000, "Sandy Mann"


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