Formula to determine range

[DavidObeid]

I used to know how to do this, but being a Monday morning my head isn'
working.

How do I get my spreadsheet to automatically determine the last cel
that contains data in a column and return the actual cell reference

 

[John Wilson]

David,

range("A65536").End(xlUp).address

John

 

[Don Guillett]

x=cells(65536,"a").end(xlup).address

 

[Anders S]

David,

Two ways (on Col A)

range("A1").End(xlDown).Address
Cells(rows.Count,1).end(xlUp).Address

Or if you want the first empty cell

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address
Range("A1").End(xlDown).Offset(1, 0).Address

HTH
Anders Silven

 

[Gord Dibben]

David

If the data is NUMERIC you can enter this in a cell.

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

Gord Dibben XL2002

 

[DavidObeid]

Thanks guys

 

[DavidObeid]

Gord,

I used =ADDRESS(MATCH(9.99999999999999E+307,A:A),4) and it worked
great.

Now my problem is that I can't seem to get the result of that formula
to work within another formula.

For example, let's say that I want to count all of the occurances of
the number 1 in cells A2 down to the result of
=ADDRESS(MATCH(9.99999999999999E+307,A:A),4)

When I tried the following:

=countif(A2:ADDRESS(MATCH(9.99999999999999E+307,A:A),4),1)

I got an error message from Excel.

What am I doing wrong now?

Dave

 

[Peo Sjoblom]

Use this instead

=COUNTIF(OFFSET($A$2,,,COUNTA($A:$A)),1)

 

[DavidObeid]

Thanks Peo,

But that formula doesn't work.

I tested it on the following:

A B C
1
2 6 5 6
3 1 2 4
4 6 1 3
5 2 4 5
6 3 5 4

And it returned a 1, when there are actually 2 occurances of the numbe
1 in the range I need to search.

Help

 

[Peo Sjoblom]

I thought you were using a single column, use this amendment

=COUNTIF(OFFSET($A$2,,,COUNTA($A$2:$A$65536),COUNTA($2:$2)),1)

 

[DavidObeid]

I got it.

I had to use

=COUNTIF($A$2:OFFSET($C$2,0,0,COUNT($A:$A)),1)

Thanks for pointing me in the right direction.