Hello, I have a range (D9:DP496) 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("D9:DP496") < Format ("#") Then Cells.Value = ""
End If

try this and see if it works for you


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

--


Gary


"alistre" wrote in message
oups.com...
Hello, I have a range (D9:DP496) 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("D9:DP496") < Format ("#") Then Cells.Value = ""
End If

This should work.......

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

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

Application.ScreenUpdating = True
End Sub


--
Cheers
Nigel



"alistre" wrote in message
oups.com...
Hello, I have a range (D9:DP496) 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("D9:DP496") < Format ("#") Then Cells.Value = ""
End If

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

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

--
Cheers
Nigel



"Nigel" wrote in message
...
This should work.......

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

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

Application.ScreenUpdating = True
End Sub


--
Cheers
Nigel



"alistre" wrote in message
oups.com...
Hello, I have a range (D9:DP496) 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("D9:DP496") < Format ("#") Then Cells.Value = ""
End If

Worked like a charm. You're a genious.

Nigel wrote
This should work.......

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

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

Application.ScreenUpdating = True
End Sub


--
Cheers
Nigel



"alistre" wrote in message
oups.com...
Hello, I have a range (D9:DP496) 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("D9:DP496") < Format ("#") Then Cells.Value = ""
End If