Counta function counting blank cells

You could try http://dmcritchie.mvps.org/excel/join.htm#trimall

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?

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

Sub TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall 
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If  
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

If this does not help, would you like to post the workbook and what you want to achieve?

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.