Lowest row with data in Col A

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
591
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

How do I find the lowest row (highest row number) in column A that contains some data?

Thanks
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi The Idea Dude.

Test this formula to find last row number in Column A:

{=(MAX((A1:A65535<>"")*ROW(A1:A65535)))-ROW(A1:A65535)+1}

formula is Array Entered formula.

Regards Sir Vili.
This message was edited by Sir Vili on 2002-10-14 08:01
 
Upvote 0
On 2002-10-14 06:47, The Idea Dude wrote:
Hi all,

How do I find the lowest row (highest row number) in column A that contains some data?

Thanks

If col A houses data of numeric type, then

=MATCH(9.99999999999999E+307,A:A)

If col A houses data of text type, then

=MATCH(REPT("z",50),A:A)

If col A houses data of mixed type, then

=MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",50),A:A))

will compute the desired row number.
 
Upvote 0
On 2002-10-14 08:35, Aladin Akyurek wrote:
On 2002-10-14 06:47, The Idea Dude wrote:
Hi all,

How do I find the lowest row (highest row number) in column A that contains some data?

Thanks

If col A houses data of numeric type, then

=MATCH(9.99999999999999E+307,A:A)

If col A houses data of text type, then

=MATCH(REPT("z",50),A:A)

If col A houses data of mixed type, then

=MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",50),A:A))

will compute the desired row number.

Aladin, WOW that is crazy stuff. I (and I am sure many others too) would like to now how this actually works, and how you come up with this stuff!

Thanks :)

P.S. The column may contain only text, only numeric data, or a combination. Therefor to not get a #NA error, I need to use all 3 formulas.
 
Upvote 0
Aladin, is it possible to set this formula up to allow for all 3 combinations so I don't get a #NA error
 
Upvote 0
As Aladin should be in bed by now...!

9.9999999E307 is the lardest number excel can store. The match looks down the column for the number, and returns the last row with data if it can't find it. IMO, it is an open question whether match()'s behavious in this regard is considered expected, surprising or a small bug...see,e.g,

http://216.92.17.166/board/viewtopic.php?topic=17175&forum=2&start=0

for more discussion.

paddy
 
Upvote 0
On 2002-10-14 20:49, PaddyD wrote:
As Aladin should be in bed by now...!

9.9999999E307 is the lardest number excel can store. The match looks down the column for the number, and returns the last row with data if it can't find it. IMO, it is an open question whether match()'s behavious in this regard is considered expected, surprising or a small bug...see,e.g,

http://216.92.17.166/board/viewtopic.php?topic=17175&forum=2&start=0

for more discussion.

paddy

Thanks Paddy,

As I posted just above your post with a question, I will point to it again, and also ask, is it possible to find the largest number out of 3 cells if 1 or 2 of these cells contains #NA. The large(B2:B4, 1) formula wont work.

Thanks again.
 
Upvote 0
On 2002-10-14 21:16, PaddyD wrote:
array entered:

=LARGE(IF(ISNA(B2:B4)=FALSE,B2:B4),1)

Paddy

Thanks Paddy, If you could explain this to me (it works perfectly) so that I can understand exactly what's happening that would be great. :)

You (and many others on here) really do desevre fudge cookies :)
 
Upvote 0

Forum statistics

Threads
1,218,808
Messages
6,144,604
Members
450,557
Latest member
Lahoya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top