Macro to clear all rows with a "J" in them

[tonywatsonhelp]

Hi everyone,
I need a macro that can go down column A an if an cells contain "J" clear the row?

seams easy but im having trouble please help

Thanks

Tony

 

[Joe4]

Is it only exactly "J" you are looking for in each cell, or can "J" be part of a longer entry, like "Joe"?

 

[Fluff]

How about

Sub tonywatson()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Replace "J", True, xlPart, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).EntireRow.ClearContents
   End With
End Sub

 

[tonywatsonhelp]

Yes, every cell is a formula that basicly looks at the row a decides if i need to keep it, if a do the answer is "" if i dont the answer is "J" (capital J if that matters?) so its any cell with just "J"

 

[tonywatsonhelp]

Thanks Fluff,
This look great, very clever
Tony

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

As you have a formula returning the J, you could change the formula to return TRUE or "" & then just use

      Range("A:A").SpecialCells(xlFormulas, xlLogical).EntireRow.ClearContents