Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not IsNumeric not working - or is it me?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not IsNumeric not working - or is it me?
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 "Ed" wrote in message ... | 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 | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not IsNumeric not working - or is it me?
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 "Ed" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
IsNumeric with array or range | New Users to Excel | |||
opposite of IsNumeric | Excel Discussion (Misc queries) | |||
check Isnumeric, doesnot work well | Excel Programming | |||
Adding sales from a non working day to the previous working day | Excel Programming |