Removing non numeric rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan M - 24 Jan 2006 10:17 GMT

HI I have a spreadsheet imported from a csv file . In column A I have a list
of stock numbers which are 6 digit numerals. The file also has header columns
and page headers etc which I need to remove so that I have a straight list of
stock reocrds only to be able to sort etc.

To do this I need to select all rows which do not have the numeric stock
number value in column A. is there code to select non-numeric values?

Reply to this Message

Bob Phillips - 24 Jan 2006 10:40 GMT

In an adjacent column add the formula

=IF(ISNUMBER(A1),"","X")

and then do an autofilter on that column (Data>Autofilter), and select X,
then just delete the visible rows.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> HI I have a spreadsheet imported from a csv file . In column A I have a list
> of stock numbers which are 6 digit numerals. The file also has header columns
[quoted text clipped - 3 lines]
> To do this I need to select all rows which do not have the numeric stock
> number value in column A. is there code to select non-numeric values?

Reply to this Message

Alan M - 24 Jan 2006 11:09 GMT

Sorry Bob,

I did not make it clear..this operation is all being done within a macro
which formats the listing to a clean list of all the stock records
automatically.

> In an adjacent column add the formula
>
[quoted text clipped - 21 lines]
> > To do this I need to select all rows which do not have the numeric stock
> > number value in column A. is there code to select non-numeric values?

Reply to this Message

Bob Phillips - 24 Jan 2006 13:07 GMT

For i = Cells(Rows.Count,"A").End(xlUp).Row
If Not Isnumeric(Cells(i,"A").Value) Then
Rows(i).Delete
End If
Next i

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

> Sorry Bob,
>
[quoted text clipped - 27 lines]
> > > To do this I need to select all rows which do not have the numeric stock
> > > number value in column A. is there code to select non-numeric values?

Reply to this Message