How do you code last row in a nested formula?

[dlow]

I asked this question in the formula forum: http://www.excelforum.com/excel-form...ed-ranges.html

I reported it & asked that it be deleted I guess its more of a VBA question since thats really how I am doing my work anyway.

So I know I am going to need to use cells just not sure of the syntax for this little nasty:

Please Login or Register  to view this content.

This is the recorded Macro which is less than helpful for me to understand what I need to do

Please Login or Register  to view this content.

I need to drop the "A46" & do a lastrow via cells I just never done it before & could use some help. TIA

[alansidman]

Look here for an example of how to do this:

http://www.ozgrid.com/forum/showthread.php?t=56370

[dlow]

BoOm Shaka Lacka thx @alansidman +1 rep & Solved, believe it or not I have been looking for this solution since Sunday.

[Paul]

One thing to note about that OzGrid solution, it's 8 years old and doesn't account for Excel 2007+ million+ rows. It starts looking up from row 65536 (max in Excel 2003-) to find the last used row. If your data contains more than that many rows, you won't get the correct last row. This may not apply to your situation, but for anyone else reading this thread in the future please take that into account.

It's probably better to use something along the lines of:

Please Login or Register  to view this content.

Note: The formula in the above code is from the Oz page, not the solution to this problem.

[dlow]

Thx Paul right-on thats what I wound up using.

For anyone who came here from my prior post here: [http://www.excelforum.com/excel-form...-formula.html]
Just want to mention that martindwilson is saying that my formula is wrong & I was willing to except that, but when I modified it the way he says it should be it changed the result & gives me the wrong answer.
So my formula is correct & whether it was or not wasnt my question anyway.
I simply needed to know how to reference the last row for my formula, it didnt have to be programmatically. I am flexible & would have been fine with either.

I do believe the answer he could have offered if he knew it would have been: INDIRECT LOOKUP ROW


In summary this:=IF(COUNTIF(A2:A39:A2:A39,A2)>1,A2,"") is a valid formula.
It will give a differant result than: =IF(COUNTIF(A2:A39,A2)>1,A2,"") which happens to give me the wrong result for what I am doing.

+1 Rep point for Pauls thoroughness