Delete all non-numerics

  • Thread starter Thread starter Mrs. Robinson
  • Start date Start date
M

Mrs. Robinson

I'm in the middle of a macro to extrapolate a row of numbers and then use the
numbers as a basis to extrapolate more information. I'm trying to delete all
the blank cells and cells with text in them, leaving behind only the numbers.
Is this possible? The worksheet from which the column is pulled is
formatted as general.

Thanks,
Mrs. Robinson
 
Mrs. Robinson,

For column B:

With Range("B:B")
..SpecialCells(xlCellTypeConstants, 2).Delete
..SpecialCells(xlCellTypeBlanks, 2).Delete
End With

HTH,
Bernie
MS Excel MVP
 
Sorry, poor copy/paste/edit. The

..SpecialCells(xlCellTypeBlanks, 2).Delete

doesn't need the 2...

HTH,
Bernie
MS Excel MVP
 
Sub Way()
Dim yourRange As Range
Dim aLastRow As Long
Dim i As Variant

aLastRow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Set yourRange = Range("B1:B" & aLastRow)

For Each i In yourRange
If Not IsNumeric(i) Then i.Delete
If IsEmpty(i) Or IsNull(i) Then i.Delete
Next
End Sub
 
Columns, not rows? Are you sure this is what was wanted?

The OP wants a per row, then per cell test, and only delete
(clear)those cells' data. You code appears to delete entire columns.
 
It worked, I see below I used both "column" and "row" ... my mistake. I'm a
techno-geek.
 
Back
Top