PDA

View Full Version : Solved: how to diferentiate between text and value?



maryam
06-06-2007, 04:00 AM
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
06-06-2007, 05:09 AM
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

maryam
06-07-2007, 02:00 AM
I get type mismatch when I change the string

Bob Phillips
06-07-2007, 02:07 AM
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
06-07-2007, 03:22 AM
lol, I made a mistake. Thanks the codes work well for my purpose.