VBA Find specific text in Range, Delete row

[mfh1287]

I have a code that works great for finding text in a range and deleting the entire row, but I can't tweak it to have it delete a row if it finds specific text within text. To explain, I am using this for an inventory tracker. In column B I have all of the product skus. I need to delete any row that has a sku in column B that ends in "-O". We use "-O" to signify that product as an open box product, and I don't need it in this tracker. Here is the code that I am using (I understand that the way I have it set up is looking for an exact match)

'Delete the "Open Box" Rows
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("B1:B" & Cells(Rows.Count, "B"). _
End(xlUp).Row), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "-O" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.delete

 

[Rick Rothstein]

kindly help me with this issue.
i have data in column A, A1:A999.
in some rows i have "Event"
in some rows i have "Eventdate"
if i want to delete those rows with "event" what to do?
example date
A1:[Event "Corus Wijk aan Zee"]
A2:[Site "Wijk aan
A3:[EventDate "?"]
A4:[Event "Griesbach"]
A5:[ECO "B93"]
A6:[Event "LAT-ch Tal"]
A7:[EventDate "1849.??.??"]
A8:[Event "Paris"]

HERE i want to delete rows 1,4,6,8.which contains the "Event"

If those square brackets are part of the cell text, then give this a try...

Sub DeleteSKUminusOrows()
  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    .Replace "[Event *", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.Select
  End With
End Sub

 

[evergreen1]

thanks for the response.

[Assuming that [] are not actually present try/QUOTE]
what have you shown after if it is two sqaure brackets, the answer is yes. the data is as per the example shown in my first post.
i tried your macro with zero results.

please see post No.5. the text is within the square brackets.because of that may be the macro is not giving the results.thanks

 

[Rick Rothstein]

thanks for the response.

please see post No.5. the text is within the square brackets.because of that may be the macro is not giving the results.thanks

Did you see my code in Message #11 yet?

 

[evergreen1]

If those square brackets are part of the cell text, then give this a try...

Sub DeleteSKUminusOrows()
  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    .Replace "[Event *", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.Select
  End With
End Sub

Rick Rothstein, thanks a lot for the suggestion

we have reached half way.the code replaces all event rows with N/A. may i ask you is it possible to make those rows deleted ?

 

[Rick Rothstein]

we have reached half way.the code replaces all event rows with N/A. may i ask you is it possible to make those rows deleted ?

Sorry, I posted my test code which just selected the rows... changing Select to Delete finishes the job...

Sub DeleteSKUminusOrows()
  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    .Replace "[Event *", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.[COLOR=#FF0000][B]Delete[/B][/COLOR]
  End With
End Sub

 

[evergreen1]

Sorry, I posted my test code which just selected the rows... changing Select to Delete finishes the job...

Sub DeleteSKUminusOrows()
  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    .Replace "[Event *", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.[COLOR=#FF0000][B]Delete[/B][/COLOR]
  End With
End Sub

thanks Rick,
with the edited code, everything goes fine. have a nice day.

 

[evergreen1]

Hi Rick,

Code:

Sub DeleteSKUminusOrows()   With Intersect(Columns("A"), ActiveSheet.UsedRange)     .Replace "[Event *", "#N/A", xlPart     .SpecialCells(xlConstants, xlErrors).EntireRow.[COLOR=#FF0000][B]Delete[/B][/COLOR]   End With End Sub

i would like to ask you a help. kindly provide an edited version of your code so that i can accomplish this task.

[Event "Monte Carlo"]	X			[Event		[Site "Monte Carlo MCO"]
[Site "Monte Carlo MCO"]						[Date "1902.02.17"]
[Date "1902.02.17"]						[Result "1-0"]
[EventDate "1902.??.??"]	X			[Eventdate		[White "Adolf Albin"]
[Round "9"]	X			[Round		[Black "Louis R Eisenberg"]
[Result "1-0"]						[Site "New York, NY USA"]
[White "Adolf Albin"]						[Date "1924.03.18"]
[Black "Louis R Eisenberg"]						[Result "0-1"]
[ECO "C50"]	X			[ECO		[White "Alexander Alekhine"]
[WhiteElo "?"]	X			[WhiteElo		[Black "Emanuel Lasker"]
[BlackElo "?"]	X			[BlackElo		[Site "London"]
[PlyCount "85"]	X			[Plycount		[Date "1851.06.21"]
[Event "New York"]	X					[Result "1-0"]
[Site "New York, NY USA"]						[White "Adolf Anderssen"]
[Date "1924.03.18"]						[Black "Kieseritzky"]
[EventDate "1924.03.16"]	X
[Round "3"]	X
[Result "0-1"]
[White "Alexander Alekhine"]
[Black "Emanuel Lasker"]
[ECO "D35"]	X
[WhiteElo "?"]	X
[BlackElo "?"]	X
[PlyCount "72"]	X
[Event "London"]	X
[Site "London"]
[Date "1851.06.21"]
[EventDate "?"]	X
[Round "?"]	X
[Result "1-0"]
[White "Adolf Anderssen"]
[Black "Kieseritzky"]
[ECO "C33"]	X
[WhiteElo "?"]	X
[BlackElo "?"]	X
[PlyCount "45"]	X

<tbody>
</tbody>

1)i have data in col A.
2)for illustrstion purpose i have collected some rows from in between the data rows.
3)the data is found in A1:A36 (36 records)
4)I have marked X on certain records in col B. this is not part of the file. it is only for illustration.I want those rows with X on col B to be deleted.But the formula or code should not have any connection with col B.or any other col except col A.
5)I have typed the first word in col E for guidance only. the words are preceded by a single sqare bracket.
6)In col G i have shown the outcome. it will be 12 records.
7)i want an edited macro on the code given by Rick. thanks.

 

[Rick Rothstein]

This macro should do what you want (note that I have highlighted the words controlling what rows to delete in red... it is a comma-delimited list)...

Sub DeleteSKUminusOrows()
  Dim X As Long, Words() As String
  Const DeletionWords As String = [B][COLOR=#FF0000]"[Event,[EventDate,[Round,[ECO,[WhiteElo,[BlackElo,[Plycount"[/COLOR][/B]
  Words = Split(DeletionWords, ",")
  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    For X = 0 To UBound(Words)
      .Replace Words(X) & " *", "#N/A", xlPart, , False
    Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[evergreen1]

thanks a lot Rick

this is perfect.

can I ask something else?
In col A data are enclosed within sqare brackets.
how can the square brackets be removed from either side of the string?

 

[Rick Rothstein]

can I ask something else?
In col A data are enclosed within sqare brackets.
how can the square brackets be removed from either side of the string?

Like this maybe...

Sub DeleteSKUminusOrows()[COLOR=#FF0000][/COLOR]
  Dim X As Long, Words() As String
  Const DeletionWords As String = "[Event,[EventDate,[Round,[ECO,[WhiteElo,[BlackElo,[Plycount"
  Words = Split(DeletionWords, ",")
  With Intersect(Columns("A"), ActiveSheet.UsedRange)
    For X = 0 To UBound(Words)
      .Replace Words(X) & " *", "#N/A", xlPart, , False
    Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
[COLOR=#008000][B]    .Replace "[", "", xlPart
    .Replace "]", "", xlPart
[/B][/COLOR]  End With
End Sub

The two lines highlighted in green are what I added to my previously posted code to remove the square brackets.