+ Reply to Thread
Results 1 to 9 of 9

Count number of non-blank cells in a range

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Count number of non-blank cells in a range

    I have a file........see enclosed.

    I want to count the number of non-blank cells in a range from A to Z along a given row.

    Then once the loop is done i want it to tell me the row which had the most non-empty cells but its not working.
    Attached Files Attached Files

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Count number of non-blank cells in a range

    Hi welchs101, how you display this is up to you but the following procedure will count everything (even the headers).
    Please Login or Register  to view this content.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Count number of non-blank cells in a range

    FYI - you can use WorksheetFunction.CountA() to count the number of non-empty cells in a given range.
    Hope that helps,

    Colin

    RAD Excel Blog

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Count number of non-blank cells in a range

    If you want a count of both empty and non empty cells, try:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Count number of non-blank cells in a range

    Quote Originally Posted by Colin Legg View Post
    FYI - you can use WorksheetFunction.CountA() to count the number of non-empty cells in a given range.
    Do you apply that to the whole CurrentRegion at once?

  6. #6
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Count number of non-blank cells in a range

    Wow that shortens the code!
    Please Login or Register  to view this content.
    Nice one Colin!

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Count number of non-blank cells in a range

    hi Mordred,

    your code is cool!

    But i was trying to count the number of non-blank cells in a given range and that range was a row from about A to Z. I then wanted to know which row had the most non-blank entries. Your code, while very very cool (and i can use it in another place), counts the number of non-blank cells in the "entire" range of a current region where c5 is in that region. I am not guaranteed that c5 is in the region of interest.

  8. #8
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Count number of non-blank cells in a range

    Hi welchs101, The following code works base on you selecting a whole row and then running the code, which is also based on the workbook you provided where the first fill column is at C.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Count number of non-blank cells in a range

    thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1