Is list1 a range with more than one column?

If it is, then
IsNumeric(Sheets(ToSheet).Range("List1").Rows(i))
won't work

isnumeric() wants to work on a single cell (to make any sense anyway).

But if you just wanted to clear the cells that contain numbers and no formulas,
you could use something like:

Option Explicit
Sub testme()
Dim myNumberVals As Range
Dim myRng As Range
Dim ToSheet As String

ToSheet = "Sheet1"

Set myRng = Worksheets(ToSheet).Range("List1")

Set myNumberVals = Nothing
On Error Resume Next
Set myNumberVals = Intersect(myRng, _
myRng.SpecialCells(xlCellTypeConstants, xlNumbers))
On Error GoTo 0

If myNumberVals Is Nothing Then
'msgbox "No constant numbers!
Else
myNumberVals.ClearContents
End If

End Sub

You could get the same kind of code by:
selecting the List1 range
Hitting ctrl-g (or F5 or Edit|Goto)
then Special
Then Constants
and unchecking everything but numbers (uncheck text, logicals and errors).




John wrote:

I have a for loop that I want to clear only cells w/ numbers and not cells
that contain formulas or text. The following ignores cells w/ text, but
clear formulas.
If IsNumeric(Sheets(ToSheet).Range("List1").Rows(i)) Then
Sheets(ToSheet).Range("List1").Rows(i).ClearConten ts
End If
How do I test for for a formula in a cell?

I appreciate your help, -John

--

Dave Peterson