Looking for the value that indicates last row number that has filled cells

[AA Arens]

Hi,

I am looking for the value that indicates last row number that still has filled cells in Column A.

From another open file: FSR.XLS!(...).

As formula in a cell, not VB code. There is no empty value somewhere in between the rows, so at the end of the CSV the rows are empty.

For example:

Row# Column A
1 554
2 323
3 987
4 334
5 (empty)
6 (empty)
etc.

Value in cell is 4

Thank you, Bart

 

[GS]

AA Arens explained :

Hi,

I am looking for the value that indicates last row number that still has
filled cells in Column A.

From another open file: FSR.XLS!(...).

As formula in a cell, not VB code. There is no empty value somewhere in
between the rows, so at the end of the CSV the rows are empty.

For example:

Row# Column A
1 554
2 323
3 987
4 334
5 (empty)
6 (empty)
etc.

Value in cell is 4

Thank you, Bart

If the cells are contiguous then use the COUNTA() function. This will
return the number of non-empty cells, which if no blanks should also be
the last row number...

=COUNTA(A:A)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

Hi,

I am looking for the value that indicates last row number that still has filled cells in Column A.

From another open file: FSR.XLS!(...).

As formula in a cell, not VB code. There is no empty value somewhere in between the rows, so at the end of the CSV the rows are empty.

For example:

Row# Column A
1 554
2 323
3 987
4 334
5 (empty)
6 (empty)
etc.

Value in cell is 4

Thank you, Bart

If GS's formula doesn't work because the rows are non-contiguous, you can try:

=LOOKUP(2,1/(LEN($A:$A)>1),ROW($A:$A))

To refer to another open book, the easiest way would be to select the column in the open book and Excel will format the result accordingly. But you need to also include the sheet name:

=LOOKUP(2,1/(LEN([FSR.XLS]Sheet1!$A:$A)>1),ROW([FSR.XLS]Sheet1!$A:$A))

 

[Claus Busch]

Hi,

Am Sat, 23 Feb 2013 22:26:02 -0800 (PST) schrieb AA Arens:

Row# Column A
1 554
2 323
3 987
4 334
5 (empty)
6 (empty)
etc.

Value in cell is 4

or:
=MATCH(0,A:A,-1)


Regards
Claus Busch

 

[Ron Rosenfeld]

or:
=MATCH(0,A:A,-1)


Regards
Claus Busch

Claus,
That returns an incorrect answer if there are 0's or negative numbers in the column

 

[Claus Busch]

Ron,

Am Sun, 24 Feb 2013 10:26:16 -0500 schrieb Ron Rosenfeld:

That returns an incorrect answer if there are 0's or negative numbers in the column

that's right. The formula is only for use with positive numbers greater
0

Regards
Claus Busch