Find/Replace After A5

[jonathann3891]

I have this bit of code that will find and replace text

   Columns("A").Replace "*section*", "#N/A", xlPart
    Columns("A").Replace "*show*", "#N/A", xlPart
    On Error Resume Next
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0

Is there anyway to make it start searching after A5?

I have two statements that are exactly the same. The one in A5 needs to stay put, the other one needs to be deleted.

 

[Rick Rothstein]

Try modifying the code you posted like this...

  With Range("A5", Cells(Rows.Count, "A").End(xlUp))
    .Replace "*section*", "#N/A", xlPart
    .Replace "*show*", "#N/A", xlPart
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With

Can we assume that the words "section" or "show" will never be the first word in the cell? If it could be, and if its first letter is upper case, your Replace method calls could fail unless you specifically include the MatchCase argument (set to False).

 

[jonathann3891]

That worked great, I just had to change the Range to start at A6.

Thanks Rick! The original code I got was from you also.

 

[Rick Rothstein]

That worked great, I just had to change the Range to start at A6.

Sorry, I missed that you said "after A5"

Thanks Rick! The original code I got was from you also.

Yes, I know... I recognized it. :wink: