Clearing Non-numerical CellCvalues

[alistre]

Hello, I have a range (D9P496) that contains numerical values and
text. I want to clear all the cells that do not have numerical values.
I attempted it below but failed miserably. As you can see I am a new
learner to VBA. Can you help?

If Workbooks("Regional Banks.xls").Worksheets("Regional
Banks").Range("D9P496") <> Format ("#") Then Cells.Value = ""
End If

 

[GKeramidas]

try this and see if it works for you


Sub test()
Dim cell As Range
For Each cell In Range("D9P496")
If IsNumeric(cell.Value) Then
cell.Value = cell.Value
Else
cell.Value = ""
End If
Next
End Sub

 

[Nigel]

This should work.......

Sub ClearArea()
Dim Cell As Range
Application.ScreenUpdating = False

With Sheets("Sheet1")
For Each Cell In .Range("D9P496")
If Not IsNumeric(Cell.Value) Then Cell.Value = ""
Next
End With

Application.ScreenUpdating = True
End Sub

 

[Nigel]

Forgot to mention - change the Sheet1 reference as required, in your case
this would be.....

With Sheets("Regional Banks") not With Sheets("Sheet1")

 

[alistre]

Worked like a charm. You're a genious.

Nigel wrote