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

 

[evergreen1]

thanks Rick. works perfect

can i ask something more?
in the following worksheet i have 15 records.

Site "Monte Carlo MCO"
Date "1902.02.17"
Result "1-0"
White "Adolf Albin"
Black "Louis R Eisenberg"
Site "New York, NY USA"
Date "1924.03.18"
Result "0-1"
White "Alexander Alekhine"
Black "Emanuel Lasker"
Site "London"
Date "1851.06.21"
Result "1-0"
White "Adolf Anderssen"
Black "Kieseritzky"

<tbody>
</tbody>

i want that records 1,2,and 3 be joined with a dash character in between.
likewise records 4 and 5 be joined .

the macro should be based on the first word of the string in each row AND NOT on rownumber.

the words are site, date ,results, white ,black


the outcome will be like this--15 rows will come to 6 rows.

Site "Monte Carlo MCO"-- Date "1902.02.17"---Result "1-0"
White "Adolf Albin"----Black "Louis R Eisenberg"
Site "New York, NY USA"---Date "1924.03.18"---- Result "0-1"
White "Alexander Alekhine"---Black "Emanuel Lasker"
Site "London"---Date "1851.06.21"---Result "1-0"
White "Adolf Anderssen"---Black "Kieseritzky"



with the excecution of the macro 15 records will be cut short into 6 records.if the records were from A1:A15 now it will be from A1:A6.

 

[evergreen1]

though i have entered all the 15 records continuously)pls see my post #21 , it is not so. there are other datarows after every 5 rows

 

[evergreen1]

Site "Monte Carlo MCO"

Date "1902.02.17"

Result "1-0"

White "Adolf Albin"

Black "Louis R Eisenberg"

1. d4 d5 2. c4 e6 3. Nf3 Nf6 4. Nc3 Nbd7 5. cxd5 exd5 6. Bf4

c6 7. e3 Nh5 8. Bd3 Nxf4 9. exf4 Bd6 10. g3 O-O 11. O-O Re8

12. Qc2 Nf8 13. Nd1 f6 14. Ne3 Be6 15. Nh4 Bc7 16. b4 Bb6

17. Nf3 Bf7 18. b5 Bh5 19. g4 Bf7 20. bxc6 Rc8 21. Qb2 bxc6

22. f5 Qd6 23. Ng2 Bc7 24. Rfe1 h5 25. h3 Nh7 26. Rxe8+ Rxe8

27. Re1 Rb8 28. Qc1 Ng5 29. Ne5 fxe5 30. Qxg5 e4 31. f6 g6

32. f4 hxg4 33. Be2 gxh3 34. Bh5 Rb2 35. Nh4 Qxf4 36. Qxf4

Bxf4 0-1

Site "New York, NY USA"

Date "1924.03.18"

Result "0-1"

White "Alexander Alekhine"

Black "Emanuel Lasker"

1. e4 e5 2. Nf3 Nc6 3. Bc4 Nf6 4. d4 exd4 5. Nxd4 Ne5 6. Bb3

Bc5 7. Bg5 d6 8. O-O Bg4 9. Ne2 h6 10. Bh4 Qe7 11. Nbc3 g5

12. Bg3 a6 13. Ba4+ Ned7 14. Qd2 O-O-O 15. Rab1 Nxe4 16. Nxe4

Qxe4 17. Nc3 Qg6 18. b4 Ba7 19. Rfe1 Nb6 20. Nd5 Nxd5 21. Qxd5

Be6 22. Qd2 h5 23. h3 h4 24. Bh2 g4 25. Kh1 Rhg8 26. Rg1 g3

27. fxg3 hxg3 28. Bxg3 Qxg3 29. Rgf1 Bxh3 30. gxh3 Rh8 0-1

<colgroup><col style="mso-width-source:userset;mso-width-alt:14592;width:299pt" width="399"> </colgroup><tbody>
</tbody>

in this way the file goes down. all the data in col B.

 

[Primordia]

I am using the below code in a file which I have multiple values (i.e. DWG, _MS, _AN, _NAS, AND _PKG) that I am trying to delete the rows if present. Is there a way to use this code to find all of these values at once and delete the rows instead of doing this code for each value. The each value works, but just trying to make it cleaner and hopefully faster since the individual value code takes a will to execute on a large excel file.

On Error Resume Next
With Columns("K")
.Replace "*DWG*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_MS*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_AN*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_NAS*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_PKG*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With

 

[Rick Rothstein]

I am using the below code in a file which I have multiple values (i.e. DWG, _MS, _AN, _NAS, AND _PKG) that I am trying to delete the rows if present. Is there a way to use this code to find all of these values at once and delete the rows instead of doing this code for each value. The each value works, but just trying to make it cleaner and hopefully faster since the individual value code takes a will to execute on a large excel file.

On Error Resume Next
With Columns("K")
.Replace "*DWG*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_MS*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_AN*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_NAS*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With
On Error Resume Next
With Columns("K")
.Replace "*_PKG*", "=1", xlWhole
.SpecialCells(xlFormulas).EntireRow.Delete
End With

You should do all the replacements first, then do the deletion at the end...

With Columns("K")
  .Replace "*DWG*", "=1", xlWhole
  .Replace "*_MS*", "=1", xlWhole
  .Replace "*_AN*", "=1", xlWhole
  .Replace "*_NAS*", "=1", xlWhole
  .Replace "*_PKG*", "=1", xlWhole
  On Error Resume Next
  .SpecialCells(xlFormulas).EntireRow.Delete
  On Error GoTo 0
End With