VBA Delete Row based on Column Data

[VBA learner ITG]

I have the below VBA code thats faulty to an extent and I was wondering if i could get your expertise to fix my issue.

For some reason the code works for exact match to the text but not for the cells that part contains the word i am looking to delete that as well.




Sub DataAmendment()


FOR COLUMN A I AM LOOKING TO DELETE THE ROW BASED ON ANY CELL THAT CONTAINS THE WORD "PUBLICATIONS" FOR EXAMPLE I HAVE WITHIN THIS COLUMN "PUBLICATIONS - AD HOC", "PUBLICATIONS - BLAH BLAH","PUBLICATIONS - SITTING" BUT I WANT THE VBA CODE TO DELETE ANY ROW WITH THE TEXT "PUBLICATIONS" NO MATTER IF ITS NOT AN EXACT MATCH.


Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "Publications -" Then
Cells(i, "A").EntireRow.Delete
End If
Next i


Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "A").Value) = "International" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(i, "A").EntireRow.Delete
End If
Next i



FOR COLUMN A I AM LOOKING TO DELETE THE ROW BASED ON ANY CELL THAT CONTAINS THE WORD "PUBLICATIONS" FOR EXAMPLE I HAVE WITHIN THIS COLUMN "PUBLICATIONS - AD HOC", "PUBLICATIONS - BLAH BLAH","PUBLICATIONS - SITTING" BUT I WANT THE VBA CODE TO DELETE ANY ROW WITH THE TEXT "PUBLICATIONS" NO MATTER IF ITS NOT AN EXACT MATCH.


Last = Cells(Rows.Count, "D").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "D").Value) = "ROI" Then
Cells(i, "A").EntireRow.Delete
End If
Next i




Last = Cells(Rows.Count, "F").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "F").Value) = "Cancelled" Then
Cells(i, "A").EntireRow.Delete
End If
Next i


Last = Cells(Rows.Count, "F").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "F").Value) = "Reprint" Then
Cells(i, "A").EntireRow.Delete
End If
Next i


FOR COLUMN H I AM LOOKING TO DELETE THE ROW BASED ON ANY CELL THAT CONTAINS A NUMBER ABOVE 0 AND TEXT THAT CONTAINS ROI AS PART OF THE TEXT




Last = Cells(Rows.Count, "H").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "H").Value) = "> 0" Then
Cells(i, "A").EntireRow.Delete
End If
Next i


Last = Cells(Rows.Count, "H").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "H").Value) = "ROI" Then
Cells(i, "A").EntireRow.Delete
End If
Next i




Last = Cells(Rows.Count, "K").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "K").Value) = "Janine Wright" Then
Cells(i, "A").EntireRow.Delete
End If
Next i




End Sub

 

[svendiamond]

Change this:

If (Cells(i, "K").Value) = "Janine Wright" Then

To

If (Cells(i, "K").Value) Like "*Janine Wright*" Then

 

[stumac]

for text you can use:

If InStr(1, (Cells(i, "A").Value), "Publications -" ) > 0 Then

for the number above 0

[COLOR=#011993]If[/COLOR] (Cells(i, "H").Value) > 0 [COLOR=#011993]Then[/COLOR]

 

[VBA learner ITG]

If InStr(1, (Cells(i, "A").Value), "Publications -" ) > 0 Then

Thank you for this it works to delete the text variable for Publications, however for some reason it deletes the header row which is called "BU" and it shouldn't be doing that I imagine as "BU" isn't in the argument to be deleted.

 

[stumac]

Strange, are you sure it is that part of the macro that is deleting the row? Are you sure that the underlying value of the cell is 'BU' and this isn't just what is being displayed.

To avoid checking the header row change:

[COLOR=#011993]For[/COLOR] i = Last [COLOR=#011993]To[/COLOR] 1 [COLOR=#011993]Step[/COLOR] -1

to

[COLOR=#011993]For[/COLOR] i = Last [COLOR=#011993]To[/COLOR] 2 [COLOR=#011993]Step[/COLOR] -1

 

[VBA learner ITG]

Thank you for your assistance, It worked like a charm.