Extract Cells Containing Numbers alone using Excel VBA

Identify cells with Numerals/Numbers using Excel VBA

Many times some text values create problem for which should be a numeric column. In those cases one can use the specialcells method to extract the cells containing numbers alone

Sub Rows_Wt_Numbers()

Dim oNOCells

On Error GoTo Err_Hdlr
Set oNOCells = Range("A1:A5").Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

For Each ocell In oNOCells
MsgBox ocell.Address
Next ocell
Err_Hdlr:
If Err <> 0 Then
If Err.Description = "No cells were found." Then
MsgBox "No cells with number found"
End If
Err.Clear
End If

End Sub

If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants:

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.

xlErrors

xlLogical

xlNumbers xlTextValues