Delete Non Numerics values

[amna77]

Hi,
Is there any way that we can look in column A and if there is non numeric value , then delete the whole row.
Any idea??????

Thanks in advance

[Juan Pablo González]

Try this:

Select column A
Hit F5 (Go to)
Click on "Special"
Select Constant or Formula (Depends if your column has constants or formulas)
Uncheck everything BUT Text
Click OK
Hit Control -
Select "Entire Row"

That should do it. If you have Booleans or Error values, then you should check them when selecting the cells.

[Mark W.]

You could apply an Advanced AutoFilter with a computed critera containing...

=ISTEXT(A2)

or...

=NOT(ISNUMBER(A2))

...depending on your specific requirements.

[NateO]

Good tip Juan, the vba equivalent:

Sub clr()

    [a:a].SpecialCells(xlCellTypeConstants, 22).EntireRow.Delete

End Sub

[Juan Pablo González]

Nate, just be careful when you're using SpecialCells in VBA. If Excel doesn't find a match, it causes a run time error... so, just add an error handler like

On Error Resume Next
[A:A].SpecialCells(...
On Error Goto 0

[amna77]

Thanks for nice tips.
I used the macro one in my sheet, and its working fine.
Thanks.

[NateO]

Juan makes a good point Amna, you may want to write is as:

Sub clr()
on error resume next
[a:a].SpecialCells(xlCellTypeConstants, 22).EntireRow.Delete
'on error goto 0 'only need this to turn off
'error trapping in a larger procedure
End Sub

I typically do write mine this way, a quick oversight in this particular thread.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-15 12:33 ]

[Al Chara]

The following code will just search the usedrange in column A and will also delete blanks:

On Error Resume Next
With Intersect(Columns(1), UsedRange)
.SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With