Formula to determine range

D

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
 
A

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
 
G

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
 
D

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
 
D

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
 
D

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.
 
Top