+ Reply to Thread
Results 1 to 7 of 7

Counta function counting blank cells

  1. #1
    Registered User
    Join Date
    06-23-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    29

    Counta function counting blank cells

    I am using the counta function to count how many cells have data in them but blank cells are being counted. If I manually do "clear contents" on any of the cells then they are not counted. This is a large spreadsheet so I'm looking for a faster way to do a global "clear contents". Any suggestions?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    Re: Counta function counting blank cells

    You could try http://dmcritchie.mvps.org/excel/join.htm#trimall
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    06-23-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counta function counting blank cells

    Thanks Jeff but, ugghh, I'm having challenges following this. I am not super knowledgeable in macros so this one is bit above my head. Do you have any other solutions?

    Thanks!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    Re: Counta function counting blank cells

    Without manually clearing those fields, a macro is required.

    Does this help?

    Paste code in a Normal module
    • Where to paste code
    • Highlight macro to copy >> Ctrl + C >> Open your workbook
    • Alt + F11 >> opens the Visual Basic Editor (VBE)
    • Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
    • Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
    • Paste code >> Ctrl + V (right side of screen)
    • Alt + Q >> exits VBE and returns to Excel
    • Back in Excel >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

    Please Login or Register  to view this content.
    If this does not help, would you like to post the workbook and what you want to achieve?

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Counta function counting blank cells

    Let's assume cell A1 is one of these blank cells that's being counted.

    Try to find out what's in the cell. In an empty cell enter this formula:

    =CODE(A1)

    What result do you get?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    06-23-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Counta function counting blank cells

    Thanks - the macro worked!

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,094

    Re: Counta function counting blank cells

    You are very welcome and thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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