Chryo, there is a problem with the specialcells approach in the prior
suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an
input cell. The specialcells would only pick up entries like 33, 44, etc,
that are not entered with an equal sign. The only approach I have found to
do what you want is to check numeric cells cell by cell and if they do not
contain any letters, then they are input cells. The routine below is an
approach on how to do.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Sub ClearNumbers()
Dim numCells As Range
Dim formulaCells As Range
Dim cellsToClear As Range
On Error Resume Next
'get numeric cells without equal sign at start
Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers)
'get all cells with an equal sign at start
Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
'exit if no matching cells
If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub
'concationate the two selections into one
If numCells Is Nothing Then
Set inputcells = formulaCells
ElseIf formulaCells Is Nothing Then
Set inputcells = formulaCells
Else
Set inputcells = Union(formulaCells, numCells)
End If
'check for entries w/o a, b, c... which would be a non
'input cell
For Each cell In inputcells
If bNumber(cell.Formula) Then
If cellsToClear Is Nothing Then
Set cellsToClear = cell
Else
Set cellsToClear = Union(cell, cellsToClear)
End If
End If
Next
'if no input cells, exit
If cellsToClear Is Nothing Then Exit Sub
'set input cells to zero
cellsToClear.Value = 0
End Sub

Private Function bNumber(cellFormula As String) As Boolean
'return false if cells contain a letter
Dim I As Integer
cellFormula = UCase(cellFormula)
For I = 65 To 90
If InStr(cellFormula, Chr(I)) 0 Then Exit Function
Next
bNumber = True
End Function


"chryo " wrote in message
...
Can anyone throw me a bone here.

I need to write some code to attache to a command button that will
check an entire spreadsheet and clear all numeric data. Basically the
spreadsheet is for cost analysis and we enter hours. I want to click
the button and have it clear all the hours from all cells and ignore
any text data.

Any help would be greatly appreciated.

Thanks!


---
Message posted from http://www.ExcelForum.com/