# Lowest row with data in Col A

#### The Idea Dude

##### Well-known Member
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 Dude,

Try : Range("A65536").End(xlup).Select

HTH

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

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.

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.

Aladin, is it possible to set this formula up to allow for all 3 combinations so I don't get a #NA error

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.

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.

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.

array entered:

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

array entered:

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

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

Replies
7
Views
264
Replies
6
Views
421
Replies
10
Views
536
Replies
0
Views
163
Replies
6
Views
233

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.

### Which adblocker are you using?

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

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