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


"Mrs. Robinson" <(E-Mail Removed)> wrote in message
news:422F5A48-AA4A-499E-8929-(E-Mail Removed)...
> 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

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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:422F5A48-AA4A-499E-8929-(E-Mail Removed)...
>> 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
>
>

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

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" <(E-Mail Removed)> wrote in message
>news:422F5A48-AA4A-499E-8929-(E-Mail Removed)...
>> 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
>

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" <(E-Mail Removed)> wrote in message
> >news:422F5A48-AA4A-499E-8929-(E-Mail Removed)...
> >> 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
> >
>