Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Count filled cells

This is a discussion on Count filled cells within the Excel Questions forums, part of the Question Forums category; I have a little problem counting filled(numbers,chars, etc) cells i used |: subtotal with filters , nothing i used COUNTA, ...

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    331

    Default Count filled cells

    I have a little problem counting filled(numbers,chars, etc) cells
    i used |:
    subtotal with filters , nothing
    i used COUNTA, nothing
    i used COUNTIF(range,"*"), nothing
    is still counting the blanks

  2. #2
    Board Regular
    Join Date
    Oct 2006
    Posts
    104

    Default Re: Count filled cells

    Have you tried

    =COUNT(A:A)

    Worked for me

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    331

    Default Re: Count filled cells

    Ah yes i forgot i used this one also, still nothing ,but thanks

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: Count filled cells

    Sounds to me like your blank cells aren't truly blank (ie they may have spaces in them which is throwing off the formulas).
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Jan 2008
    Posts
    331

    Default Re: Count filled cells

    Yes you are right , i thing is a problem when the txt file was imported

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: Count filled cells

    Cells which do have data - are there any genuine spaces in these ie spaces between words)? If not, you could simply do a Find/Replace on all the data finding spaces and replacing with nothing (ie effectively removing the spaces).
    Richard Schollar

    Using xl2013

  7. #7
    Board Regular
    Join Date
    Jan 2008
    Posts
    331

    Default Re: Count filled cells

    well i found this trim macros

    Code:
    Option Explicit
     
    Sub TrimXcessSpaces()
         'Macro Purpose: To trim all excess spaces out of cells.  This
         'eliminates issues where users have cleared the cell with a space,
         'and elimates all extra spaces at the beginning or end of a string
         
        Dim cl As Variant
         
         'Loop through cells removing excess spaces
        For Each cl In Selection
            If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
                cl.Value = WorksheetFunction.Trim(cl)
            End If
        Next cl
    End Sub
    works nice, but the big problem is it that the date is imported from sql, and the thing is that the is not correctly identify the NULL case from database

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: Count filled cells

    and the thing is that the is not correctly identify the NULL case from database
    What does this mean? Sorry I am not following you. Are you saying that these cells still have something in them?
    Richard Schollar

    Using xl2013

  9. #9
    New Member
    Join Date
    Aug 2007
    Location
    Delhi, India
    Posts
    11

    Thumbs up Re: Count filled cells

    just do Trim(cloumn) it remove all the spacing then use count formula

  10. #10
    Board Regular
    Join Date
    Jan 2008
    Posts
    331

    Default Re: Count filled cells

    I a data base if you have nothing inside in a case it is NULL, well when you export a data base or a table in excel , NULL is blank , but well it isn't

Page 1 of 2 12 LastLast

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