Solved: how to diferentiate between text and value?

[maryam]

We have an excel sheet in which we have data, but sometimes there is no good data and the cell text is no good data instread of the value. In that case we want to delete the whole row, how can we write the codes?


P T C D
#1 1 34 56 98
#2 9 No 9 10
#3 No 9 20 30
#4 1 2 3 4
#5 9 80 7 6


row 2 and 3 should be deleted from the excel sheet.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

Application.ScreenUpdating = False

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Application.Count(.Rows(i)) <> Application.CountA(.Rows(i)) Then
.Rows(i).Delete
End If
Next i

Application.ScreenUpdating = True

End With

End Sub
[/vba]

[maryam]

I get type mismatch when I change the string

[Bob Phillips]

The code works on column letters, not a value in them. There is no column "NO" prior to Excel 2007.

What was the objective of changing it to NO?

[maryam]

lol, I made a mistake. Thanks the codes work well for my purpose.