deleting rows which includes some words

[aaaaa34]

hello my friends,


in my worksheet, i want to delete entire rows which includes "Corners, Yellow cards, Red Cards" words
also i want to delete upper and lower rows of that row which we delete (these rows don't need to include any specific word, just i need to delete them too).


pls have a look to my file. delete row, upper and lower rows also - Jumpshare
thanks a lot.

 

[aaaaa34]

up.

 

[My Aswer Is This]

Are you saying if the word:
"Corners" or "Yellow cards" or "Red Cards"

Are found in column "A" of any row you want to delete the row?

And I have no ideal what this means:
"also i want to delete upper and lower rows of that row"

Do you mean the row above and below the row with these word?

 

[aaaaa34]

Are you saying if the word:
"Corners" or "Yellow cards" or "Red Cards"

Are found in column "A" of any row you want to delete the row?

And I have no ideal what this means:
"also i want to delete upper and lower rows of that row"

Do you mean the row above and below the row with these word?

exactly, above and below row of related rows
for example, if A23 has "red cards" words , i want to delete A23 , but i want to delete also A22 and A24 too
pls have a look to file. you'll understand easily what i mean.

 

[1Ronin]

Hi,

Try this:

Sub Remove_rows()

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


End Sub

Works in my tests.

Regards,

 

[Peter_SSs]

You could also try this on a copy of your workbook.

Sub RemoveRows()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Cells(1).Value = "x"
    .Cells(2, 2).Formula = "=SUMPRODUCT(--(A1:A3={""Corners"",""Yellow Cards"",""Red Cards""}))=0"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("G1"), Unique:=False
  End With
  Range("A1,B2,G1").ClearContents
End Sub

 

[Peter_SSs]

On re-checking, I see that you actually want the results still in column A, not G, so I have modified a little.

Sub RemoveRows_v2()
  Application.ScreenUpdating = False
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Cells(1).Value = "x"
    .Cells(2, 2).Formula = "=SUMPRODUCT(--(A1:A3={""Corners"",""Yellow Cards"",""Red Cards""}))=0"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("C1"), Unique:=False
  End With
  Columns("A:B").Delete
  Range("A1").ClearContents
  Columns("A").AutoFit
  Application.ScreenUpdating = True
End Sub

 

[aaaaa34]

this last code perfect, Mr. Moderator.
really perfect. Thanks a lot. Thank you so much.

i'd like to ask you if we can edit this code like this:
code will search again same three phrases, but this time it will delete that row itself, but this time it will delete one above row, three below rows.
please have a look to this file: http://jumpshare.com/v/RVFO9ZbI8po7ebAnFqrA
you'll understand so easily what i mean.

 

[aaaaa34]

Hi,

Try this:

Works in my tests.

Regards,

Thanks a lot Ronin, sth wrong with code.

 

[Peter_SSs]

this last code perfect, Mr. Moderator.
really perfect. Thanks a lot. Thank you so much.

You are welcome.

code will search again same three phrases, but this time it will delete that row itself, but this time it will delete one above row, three below rows.

If I have understood correctly, I think this should do it.

Sub Remove1Above3Below()
  Application.ScreenUpdating = False
  Rows("1:3").Insert
  With Range("A3", Range("A" & Rows.Count).End(xlUp))
    .Cells(1).Value = "x"
    .Cells(2, 2).Formula = "=SUMPRODUCT(--(A1:A5={""Corners"",""Yellow Cards"",""Red Cards""}))=0"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Cells(1, 2).Resize(2), CopyToRange:=.Cells(1, 3), Unique:=False
  End With
  Columns("A:B").Delete
  Rows("1:3").Delete
  Columns("A").AutoFit
  Application.ScreenUpdating = True
End Sub