Macro to Delete any Row in entire sheet based on Cell Values

[adibakale]

I need to erase the entire row based on 2 different cell values. I would like the Macro to all rows and columns on the sheet and delete the entire row if it finds a cell containing these 2 values:

1. WORK OF
2. RUN DATE

Thank you - your help is greatly appreciated

 

[Rick Rothstein]

I need to erase the entire row based on 2 different cell values. I would like the Macro to all rows and columns on the sheet and delete the entire row if it finds a cell containing these 2 values:

1. WORK OF
2. RUN DATE

Please clarify what you mean by "a cell containing"... do you mean both of the lines (with the number/dot/space in front) are in the same cell? If so, are they on two separate lines (as shown) within that same single cell and with the numbers in front always be 1. and 2.?

 

[adibakale]

Cells containing only the words WORK OF and RUN DATE. Both of these cells are in 2 different rows. They also contain a Date and other words. Here is exactly what each cell in each row looks like:

[TABLE="width: 212"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WORK OF 05/05/15 PAGE
[/TD]
[/TR]
[TR]
[TD]RUN DATE 05/06/15 TIME

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Cells containing only the words WORK OF and RUN DATE. Both of these cells are in 2 different rows. They also contain a Date and other words. Here is exactly what each cell in each row looks like:

[TABLE="width: 212"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WORK OF 05/05/15 PAGE
[/TD]
[/TR]
[TR]
[TD]RUN DATE 05/06/15 TIME

[/TD]
[/TR]
</tbody>[/TABLE]

I is not clear if these words are contained within a single column or not. If they can be located anywhere, then use this macro...

Sub DeleteRowsContainingWORKOFandRUNDATE()
  With Cells
    .Replace "WORK OF*", "#N/A", xlWhole
    .Replace "RUN DATE*", "#N/A", xlWhole
    'On Error Resume Next
    Intersect(Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    On Error GoTo 0
  End With
End Sub

If they are restricted to a single column, then use this instead (replace the A with the correct column letter designation)...

Sub DeleteRowsContainingWORKOFandRUNDATE()
  With Columns("[B][COLOR="#FF0000"]A[/COLOR][/B]")
    .Replace "WORK OF*", "#N/A", xlWhole
    .Replace "RUN DATE*", "#N/A", xlWhole
    'On Error Resume Next
    Intersect(Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    On Error GoTo 0
  End With
End Sub

 

[adibakale]

Thank you very much - I really appreciate it

 

[adibakale]

I am getting a

Run-time error '1004':
No cells were found.

Debug: Intersect(Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete

 

[Rick Rothstein]

I am getting a

Run-time error '1004':
No cells were found.

Debug: Intersect(Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete

That means you don't have any cells with the text you wanted to find. The error message was supposed to be suppressed by the line above which I commented out while debugging and forgot to reactivate it... remove the apostrophe from the line above the one you posted and the error message will go away.

 

[adibakale]

That worked, thank you. There are 7 spaces before the WORK OF and RUN DATE. Adding the 7 spaces in the macro worked, but is there a way to just ignore these spaces?

 

[Rick Rothstein]

That worked, thank you. There are 7 spaces before the WORK OF and RUN DATE. Adding the 7 spaces in the macro worked, but is there a way to just ignore these spaces?

In Message #3 you said "Here is exactly what each cell in each row looks like" and what you posted did not show any leading characters at all, so I did not try to account for any. The solution... put an asterisk before them in the Replace function calls...

Sub DeleteRowsContainingWORKOFandRUNDATE()
  With Cells
    .Replace "[B][COLOR="#FF0000"]*[/COLOR][/B]WORK OF*", "#N/A", xlWhole
    .Replace "[B][COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]*[/COLOR][/B]RUN DATE*", "#N/A", xlWhole
    On Error Resume Next
    Intersect(Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    On Error GoTo 0
  End With
End Sub

 

[adibakale]

my apologies - thanks