I am trying to write a macro to find if an item exists within a cell and if so keep the row on which the cell resides cell or else delete it.

For example in the cell will be the text "London, Brimingham, Bristol" and I would like to put a search cname for London and have the row kept, but if I typed in Liverpool the row would be deleted.

The search name is on another worksheet in the spreadsheet.

The code I currently have is the following:

LRow = 1

While IsEmpty(Range("K" & CStr(LRow)).Value) = False
If Range("M" & CStr(LRow)).Value < Range("SM!D6") Then
Range("A" & LRow & ":T" & LRow).Select
Selection.Delete

' Decrement counter since row was deleted

LRow = LRow - 1

End If

LRow = LRow + 1

Wend

How can I get the code Range("SM!D6") to include a subset that is the search name? I have tried various methods such as Range("*""SM!D6""*") and various other combinations but cannot get it to work.

SM is the other worksheet name and D6 is the cell where the search name is chosen.

Please any thoughts as I am really stuck.

Bulent.

One way:

Dim rCell As Range
Dim rDelete As Range
Dim sText As String

sText = LCase(Worksheets("SM").Range("D6").Text)
With ActiveSheet
For Each rCell In .Range("K1:K" & _
.Range("K" & .Rows.Count).End(xlUp).Row)
With rCell
If InStr(LCase(.Offset(0, 2).Text), sText) = 0 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 Intersect(rDelete.EntireRow, _
.Range("A:T")).Delete Shift:=xlShiftUp
End With


End WithIn article ,
Bulent wrote:

I am trying to write a macro to find if an item exists within a cell and
if so keep the row on which the cell resides cell or else delete it.

For example in the cell will be the text "London, Brimingham, Bristol"
and I would like to put a search cname for London and have the row
kept, but if I typed in Liverpool the row would be deleted.

The search name is on another worksheet in the spreadsheet.

The code I currently have is the following:

LRow = 1

While IsEmpty(Range("K" & CStr(LRow)).Value) = False
If Range("M" & CStr(LRow)).Value < Range("SM!D6") Then
Range("A" & LRow & ":T" & LRow).Select
Selection.Delete

' Decrement counter since row was deleted

LRow = LRow - 1

End If

LRow = LRow + 1

Wend

How can I get the code Range("SM!D6") to include a subset that is the
search name? I have tried various methods such as Range("*""SM!D6""*")
and various other combinations but cannot get it to work.

SM is the other worksheet name and D6 is the cell where the search name
is chosen.

Please any thoughts as I am really stuck.

Bulent.

You don't need to increase LRow if the row will not be deleted. Here
is code that does what you ask:

Sub clearList()
lRow = 1
While Cells(lRow, 11) < ""
If InStr(1, Cells(lRow, 13).Value, Sheets("SM").Range("D6")) Then
Cells(lRow, 11).EntireRow.Delete
Else
lRow = lRow + 1
End If
Wend
End Sub

HTH
Kostis Vezerides

On Oct 31, 11:37 am, Bulent
wrote:
I am trying to write a macro to find if an item exists within a cell and
if so keep the row on which the cell resides cell or else delete it.

For example in the cell will be the text "London, Brimingham, Bristol"
and I would like to put a search cname for London and have the row
kept, but if I typed in Liverpool the row would be deleted.

The search name is on another worksheet in the spreadsheet.

The code I currently have is the following:

LRow = 1

While IsEmpty(Range("K" & CStr(LRow)).Value) = False
If Range("M" & CStr(LRow)).Value < Range("SM!D6") Then
Range("A" & LRow & ":T" & LRow).Select
Selection.Delete

' Decrement counter since row was deleted

LRow = LRow - 1

End If

LRow = LRow + 1

Wend

How can I get the code Range("SM!D6") to include a subset that is the
search name? I have tried various methods such as Range("*""SM!D6""*")
and various other combinations but cannot get it to work.

SM is the other worksheet name and D6 is the cell where the search name
is chosen.

Please any thoughts as I am really stuck.

Bulent.

--
Bulent