I hope I understood your issue...I am assuming you were doing this AFTER data was entered to the sheet and not at data entry time (although there is a way to do this so that only numeric values are entered in the first place).
I would create a named range to include the target cells. I am not sure of your worksheet design, but the following example assumes a square grid of cells with the named range "rngToClear" covering the entire grid.
Here is the macro:
Option Explicit
Option Compare Text
Sub ClearNonNumeric()
Dim intMinCol As Integer
Dim intMaxCol As Integer
Dim i As Integer
Dim j As Integer
Dim lngMinRow As Long
Dim lngMaxRow As Long
Dim objSheet As Worksheet
Set objSheet = ThisWorkbook.Worksheets(1)
With objSheet
intMinCol = .Range("rngToClear").Column
intMaxCol = .Range("rngToClear").Columns.Count
lngMinRow = .Range("rngToClear").Row
lngMaxRow = .Range("rngToClear").Rows.Count
For i = lngMinRow To lngMaxRow
For j = intMinCol To intMaxCol
If Not IsNumeric(.Cells(i, j).Value) Then
.Cells(i, j).ClearContents
End If
Next j
Next i
End With
End Sub
|
