Last cell in column .

G

Guest

Guest
How do I find the last used cell in a column?
Each day a new cell is used in the column so the column gets longer.I want to use the info that is contained in the last used cell.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
On 2002-03-09 01:41, Anonymous wrote:
How do I find the last used cell in a column?
Each day a new cell is used in the column so the column gets longer.I want to use the info that is contained in the last used cell.

Does the column contain numeric or text data?
 
Upvote 0
On 2002-03-11 15:28, karraj wrote:
counta(A:A)

=COUNTA(A:A) will not count empty cells so cannot be used alone if empty cells are dispersed among the cells that contain values.
 
Upvote 0
=COUNTIF(A:A,"<>""")

will return the last used cell on that sheet, BUT, THIS IS NOT RELIABLE. So, go with Dave's solution, VBA is the only "reliable" way of knowing.
 
Upvote 0
...VBA is the only "reliable" way of knowing.

Ouch! If feel a pain in my chest! :)

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A)

...works quite nicely for numeric data. My inquiry as to the nature of the data has yet to be answered.
This message was edited by Mark W. on 2002-03-11 16:29
 
Upvote 0
On 2002-03-11 16:31, Juan Pablo G. wrote:
I know Mark... Match works GREAT for numeric data, but, we've seen more than once that THAT column doesn't have numbers...

Oh, anyway...

I didn't have the chance to respond last night... had to join by buddies for some billiards.

Consider this for determining the extent of a column of typical text values...

=MATCH(REPT("z",255),A:A)

This can be refined if a key field of a data list is fixed length (or its maximum length is known) such as an account number. So for an 8-character account number you'd use....

=MATCH(REPT("z",8),A:A)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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