Lowest row with data in Col A

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
Hi all,

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

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589

ADVERTISEMENT

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.
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
Aladin, is it possible to set this formula up to allow for all 3 combinations so I don't get a #NA error
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
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.
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
589
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 :)
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

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
Top