Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Numerics only | Excel Discussion (Misc queries) | |||
Remove Non-Numerics from String | Excel Programming | |||
How to forecast non-numerics? | Excel Worksheet Functions | |||
Formula with text and numerics | Excel Worksheet Functions | |||
Making certain a cell contains numerics | Excel Programming |