Results 1 to 2 of 2

Thread: countblank

  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countblank

    Data is entered in column a. What formula can i put in b1 that'll tell me how many blanks between a1 and the cell used in col a. I could manually find the last cell used say a2000) and then do =countblank(a1:a2000). However the last cell will be different every month so what'd really be useful is a forumla that'd take this into account.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: countblank

    The following UsedRows Function will return the number of rows used in the active sheet. Provided that column A extends as far as the furthest column in terms of numbers of rows used. Just copy this into a VBA module of your workbook.

    Function UsedRows()
    Application.Volatile
    UsedRows = ActiveSheet.UsedRange.Rows.Count
    End Function

    If you use that together with Counta, you should get what you are looking for. Use =UsedRows()-COUNTA(A:A)

    Hope that helps

    Andrew

Posting Permissions

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