Delete all non-numerics

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
 
B

Bernie Deitrick

Mrs. Robinson,

For column B:

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

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Sorry, poor copy/paste/edit. The

..SpecialCells(xlCellTypeBlanks, 2).Delete

doesn't need the 2...

HTH,
Bernie
MS Excel MVP
 
J

Jeff

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
 
C

Capt. Cave Man

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.
 
M

Mrs. Robinson

It worked, I see below I used both "column" and "row" ... my mistake. I'm a
techno-geek.
 
Top