Delete all non-numerics
 

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

Delete all non-numerics
 

Mrs. Robinson,

For column B:

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

HTH,
Bernie
MS Excel MVP


"Mrs. Robinson" wrote in message
...
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

Delete all non-numerics
 

Sorry, poor copy/paste/edit. The

..SpecialCells(xlCellTypeBlanks, 2).Delete

doesn't need the 2...

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mrs. Robinson,

For column B:

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

HTH,
Bernie
MS Excel MVP


"Mrs. Robinson" wrote in message
...
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

Delete all non-numerics
 

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


"Mrs. Robinson" wrote:

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

Delete all non-numerics
 

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.




On Wed, 8 Apr 2009 08:32:18 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org wrote:

Mrs. Robinson,

For column B:

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

HTH,
Bernie
MS Excel MVP


"Mrs. Robinson" wrote in message
...
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

Delete all non-numerics
 

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

"Capt. Cave Man" wrote:


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.




On Wed, 8 Apr 2009 08:32:18 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org wrote:

Mrs. Robinson,

For column B:

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

HTH,
Bernie
MS Excel MVP


"Mrs. Robinson" wrote in message
...
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