Not IsNumeric not working - or is it me?

[Ed]

I'm trying to evaluate all cells down a column and delete all rows with a
non-number in the cell. It was working fine - until I hit "*85"!! The *
threw everthing off. So I was trying to use Not IsNumeric to evaluate the
second character of the cell value. But my code is not working. Apparently
I'm using it wrong. If anyone can help. I'd appreciate it.

Ed

Do
If .Cells(i, 1).Value = "" Or _
.Cells(i, 1).Value = " " Then
.Cells(i, 1).EntireRow.Delete
j = .Rows.Count
****** Problem area ******
ElseIf Len(.Cells(i, 1).Value) > 1 And _
Not IsNumeric(Left(.Cells(i, 1).Value, 2)) Then
****** Problem area ******
.Cells(i, 1).EntireRow.Delete
j = .Rows.Count
Else
i = i + 1
End If
Loop Until i > j

 

[William]

Hi Ed

A different approach to yours but it may help

Sub test()
Application.ScreenUpdating = False
Dim r As Range
With ActiveSheet
..AutoFilterMode = False
..Columns("B:B").Insert Shift:=xlToRight
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
r.Offset(0, 1).FormulaR1C1 = "=ISNUMBER(RC[-1])"
r.Offset(0, 1).Formula = r.Offset(0, 1).Value2
If Application.CountIf(.Range("B:B"), "False") > 0 Then
..Columns("B:B").AutoFilter Field:=1, Criteria1:="FALSE"
Set r = r.Offset(0, 1).SpecialCells(xlCellTypeVisible)
..AutoFilterMode = False
r.EntireRow.Delete
End If
..Columns("B:B").EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I'm trying to evaluate all cells down a column and delete all rows with a
| non-number in the cell. It was working fine - until I hit "*85"!! The *
| threw everthing off. So I was trying to use Not IsNumeric to evaluate the
| second character of the cell value. But my code is not working.
Apparently
| I'm using it wrong. If anyone can help. I'd appreciate it.
|
| Ed
|
| Do
| If .Cells(i, 1).Value = "" Or _
| .Cells(i, 1).Value = " " Then
| .Cells(i, 1).EntireRow.Delete
| j = .Rows.Count
| ****** Problem area ******
| ElseIf Len(.Cells(i, 1).Value) > 1 And _
| Not IsNumeric(Left(.Cells(i, 1).Value, 2)) Then
| ****** Problem area ******
| .Cells(i, 1).EntireRow.Delete
| j = .Rows.Count
| Else
| i = i + 1
| End If
| Loop Until i > j
|
|

 

[Don Guillett]

try
Sub delnonnum()
For i = Cells(Rows.Count, 1).End(xlUp).row To 1 Step -1
If Not IsNumeric(Cells(i, 1)) Or Cells(i, 1) = "" Then Rows(i).Delete
Next
End Sub

 

[KL]

Hi Ed,

"*" isn't the problem, it is the way you loop through cells and delete rows.
You need to delete rows in the inverted order. Also, you have a number of
redundant conditions. Try this code:

Sub test()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
ScreenUpdating = False
For i = LastRow To 1 Step -1
If IsEmpty(Cells(i, 1)) Or Not IsNumeric(Cells(i, 1)) Then
Rows(i).Delete
End If
Next
End Sub

Regards,
KL