A macro for deleting lines of text

[melara]

Hi I need to delete all lines except those containing the Text NMI, FromDate>,
To Date>

Can someone tell me where I'm going wrong with this Macro when I run it, it
deletes all lines?

Public Sub DeleteNonSpecific Rows()
Dim rCell As Range
Dim rDelete As Range

For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If Not (Text) = " FromDate>" Or _
(Text) = " ToDate>" Or _
UCase(.Text) = " NMI> ") Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

 

[OssieMac]

Hi Melara,

You have some parenthesis in the wrong place and leading dots missing from
..Text After fixing these the code seems to test OK. However, the leading and
trailing spaces in the text being compared worry me so below is your code
simply corrected and below that I have included a line where I would use
Instr function and leave the leading and trailing spaces off the strings to
look for.

Public Sub DeleteNonSpecificRows()
Dim rCell As Range
Dim rDelete As Range

For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If Not (.Text = " FromDate>" Or _
.Text = " ToDate>" Or _
UCase(.Text) = " NMI> ") Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next rCell

If Not rDelete Is Nothing Then rDelete.EntireRow.Delete

End Sub


Using Instr function for the test.

If Not (InStr(.Text, "FromDate>") > 0 Or _
InStr(.Text, "ToDate>") > 0 Or _
InStr(UCase(.Text), "NMI") > 0) Then