Clearing only cells w/ numbers

[John]

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).ClearContents
End If
How do I test for for a formula in a cell?

I appreciate your help, -John

 

[Patrick Molloy]

DIM rSource as Range

SET rSource = somerange
if LEFT(TRIM(rSource.Formula),1)="=" Then
'' its a formula -- leave it
end if

 

[Stefi]

Try

ActiveCell.HasFormula

looping through cells one by one.


Regards,
Stefi

„John†ezt írta:

 

[Rick Rothstein]

Use the HasFormula property of the range to see if it contains a formula or
not. I would also consider using a With...End With block to cut down on the
lengthy repeats. Try this...

With Sheets(ToSheet).Range("List1").
If IsNumeric(.Rows(i).Value) And Not .Rows(i).HasFormula Then
.Rows(i).ClearContents
End If
End With

 

[Dave Peterson]

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).