detecting ints

  • Thread starter Thread starter tehwa
  • Start date Start date
T

tehwa

Hi,

I wish to loop through the first column of a workbook, and delete any
rows that do not have a number in them.

I have the looping sorted, but for the life of me I can't find a way
for excel to detect a number:

//look through the sheet for rows that do not begin with a number
...If Cells(counter, 1).Value <> ...?
// where "...?" = a number.

// delete teh row
Rows(counter).Delete

Can anyone tell me how to define this in the formula, is there some way
of sayaing:

<> (1 To 1000)

i have done some searching, and tried many different things, to no
avail.

Thanks in advance,

tehwa
 
One way:

Public Sub DeleteNonNumbers()
Dim rCell As Range
Dim rDelete As Range
On Error Resume Next
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
On Error Resume Next ' in case no blanks
Set rDelete = .SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
For Each rCell In .Cells
If Not IsNumeric(rCell.Value) Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
End With
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

Note that this will delete dates. If you want to preserve dates, use

If Not (IsNumeric(rCell.Value) Or IsDate(rCell.Value)) Then

instead of

If Not IsNumeric(rCell.Value) Then
 
Back
Top