Deleting Rows after a keyword 1

[jupops]

Hi All

Could you please help. Is it possible to write a VBA macro that will search a worksheet and when there is a specific word in a cell, (for example apple in column A). Then delete that row and the following 4 rows and then carry on looking though the spreadsheet to see if apple appears again, and once again delete all the 5 rows. Thank you for help.

Regards
Jupops

 

[Zathras]

Can you provide a few more details please...

Is it always a specific column?
If it is a specific columnm, is it always column "A"?
Should the entire cell value equal to "apple" or is that word only part of the cell?

So for example, if any cell on row 6 contains "this applet requires Java" would you want to delete rows 6 thru 10?

 

[jupops]

Hi Zaphras

It will always be in Column A and the keyword will always be filled with apple, it is not split over two cells. Thank you for your help.

Regards
Jupops

 

[Zathras]

Here's one way:
[blue]

Option Explicit

Sub test()
  Delete5Rows ("apple")
End Sub

Sub Delete5Rows(KeyWord As String)
Const COL_TEST = 1

[green]

' Column "A"

[/color]

Dim nRow As Long
Dim nLastRow As Long
Dim sRowsToDelete As String
  With ActiveSheet.UsedRange
    nLastRow = .Rows.Count + .Row - 1
  End With
  For nRow = 1 To nLastRow
    If Cells(nRow, COL_TEST) = KeyWord Then
      sRowsToDelete = nRow & ":" & (nRow + 4)
      Range(sRowsToDelete).EntireRow.Delete
    End If
  Next nRow
End Sub

[/color]

 

[jupops]

Good Morning Zathras

This is perfect, thank you for your help.

Regards
Jupops

 

[josecervmon]

How about deleting a single row if a cell equals zero in a certain column? For example, I want to write a macro to delete all rows that where the cells in column "L" are equal to zero. Anyway to do this without using loops?

Thanks

 

[Zathras]

Any time you say "all rows where" some condition, you are implying a loop. So the answer is no, there is no way to do that without a loop. (Note that there are two ways of expressing a loop: "For x to y" and "For each a in c&quot

In your case, a simple loop like
[blue]

    For nRow = nLastRow to 1 step -1

[/color]
should be easy to set up. By working from the bottom to the top, the act of deleting the row won't cause the next row to be skipped as it would if you worked from the top to the bottom.