Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting used cells (2000)

    I have a List and want to count the number of rows using a formula (NOT using VBA, CurrentRegion, Intersect,etc.). There are gaps in the list such that column A might finish at row 40 but column B finishes at row 32, etc.. Now I could probably use COUNTA(A:F) and MAX(), etc., but this seems very inefficient to me. Is there a neater solution out there? Cheers, Andy.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Counting used cells (2000)

    Try using Excels =Rows(range) function

    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting used cells (2000)

    If the column of interest is of numeric type, that is, it houses numbers or dates, use:

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

    If the column of interest is of alphanumeric type, that is, it houses text-formatted values, use:

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

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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