Last cell in column .
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Last cell in column .

  1. #1
    Guest

    Default

     
    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.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try

    Range("A65536").End (xlUp)

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    counta(A:A)

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    that is true...

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com