|
|
Last "Numeric Value" in a column of formulas |
|
Author |
Message |
Gavon
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
>
|
|
|
|
|
|
|