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

 

[timfritsch]

Try changing if (cell.value) = "-0" to if right(cell.value,2) = "-0"


Tim

 

[Rick Rothstein]

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

I would do it this way (no loops, relatively fast)...

Sub DeleteSKUminusOrows()
  With Intersect(Columns("B"), ActiveSheet.UsedRange)
    .Replace "*-O", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[timfritsch]

Rick - you amaze me. Awesome as always. I'm copying that code for future use.


Tim

 

[evergreen1]

I would do it this way (no loops, relatively fast)...

Sub DeleteSKUminusOrows()
  With Intersect(Columns("B"), ActiveSheet.UsedRange)
    .Replace "*-O", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

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"

 

[VoG]

Assuming that [] are not actually present try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value Like "Event *" Then Rows(i).Delete
Next i
End Sub

 

[NessPJ]

Not trying to hijack the thread here, but i seem to be hitting a very similar problem with my routine.
What i want to do is search for a line of text inside a file (which only occurs once) and then delete Cells A:R of the row containing this text + the row following after this one.

So far i have this, but i keep getting debug reports on my Row selection:

    Dim myCell, myCell1, myRow, myRange As Range
    
    Cells.Find(What:="Dernier", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Set myCell = ActiveCell
    
    Set myRow = ActiveCell.Row + 1
    Set myCell1 = Range("R" & myRow)
        
    Set myRange = Range(myCell, myCell1)
    myRange.Cells.Delete Shift:=xlUp

Any help would be greatly appreciated.

 

[VoG]

Try

Sub test2()
Dim Found As Range
Set Found = Cells.Find(What:="Dernier", LookIn:=xlFormulas, LookAt:=xlPart)
If Not Found Is Nothing Then
    Range("A" & Found.Row).Resize(2, 18).Delete shift:=xlShiftUp
End If
End Sub

 

[NessPJ]

Try
[......]

Works like a charm. Thanks!

 

[evergreen1]

Assuming that [] are not actually present try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value Like "Event *" Then Rows(i).Delete
Next i
End Sub

thanks for the response.

[Assuming that [] are not actually present try/QUOTE]
what have you shown after

Assuming that

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.