I think I'd yell at the users not to do use that technique anymore.

But if I had to, I'd have a worksheet event looking for changes and fix those
cells with just spaces entered.

Rightclick on the worksheet tab that should have this behavior and select View
code. Then paste this into that code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

On Error Resume Next
Application.EnableEvents = False
For Each myCell In Target.Cells
If myCell.HasFormula Then
'do nothing
Else
If Trim(myCell.Value) = "" Then
myCell.Value = ""
End If
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

This doesn't clean up the existing bad cells--it just stops any new cells from
being cleared this way.



DejaVu wrote:

Also, I'm doing some work on another cell.

I have this formula in J157:
=IF(COUNTA(C:C)-14=0,0,IF(C4="",COUNTA(C:C)-14,COUNTA(C:C)-15))

It looks through column C for anything entered (supposed to be customer
names). The problem is that it counts if anything is in there, and
sometimes my users hit the space bar to clear out old data. It will
count the spaces. I want this to either not count the ones with only
spaces or not allow only spaces (it could possibly delete spaces when
entered). Again, I'm not sure if this can even be done at all!!
Thanks again for your help.

DejaVu

--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=380088

--

Dave Peterson