Clear contents of any cell in column that contain a double quote in the text string

Question

I need to clear the contents of any cell in column K that contains a double quote (") in the text string. The text strings will differ some will say 3" hose, some will say 10" road so it won't be the same and it is only within that column that I need the contents of the cell cleared (not deleted, I don't want the remaining data to move).

Here is what I have tried:

Sub Macro4()
    Rows("3:3").Select
    Selection.AutoFilter
    Range("K2").Select
    ActiveSheet.Range("$A$3:$V$3225").AutoFilter Field:=11, Criteria1:="=*""*" _
      , Operator:=xlAnd
    Range("K45").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Rows("3:3").Select
    Range("E3").Activate
    Selection.AutoFilter
    Range("A4").Select
End Sub

Select column k then `Ctrl+h` then `Find what: *"*` and `Replace with:` then hit `Replace All` — simpLE MAn, Jan 08 '15 at 20:49
The problem is that I recorded that. I dont' know how to edit it to select the resulting contents and clear since the results won't always start with row 45 :( and for some reason, it caught some items but not others. Like the cells that contain 3" hose were brought up in the filter but 10" road did not... — RavenclawExcelStudent, Jan 08 '15 at 20:51
Columns("K:K").Select Selection.Replace What:="*''*", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False — RavenclawExcelStudent, Jan 08 '15 at 21:04
@RavenclawExcelStudent [STOP USING `SELECT`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Chrismas007, Jan 08 '15 at 21:28

score 1 · Answer 1 · edited May 23 '17 at 11:57

Answer posted in comments:

Columns("K:K").Select
Selection.Replace What:="''", Replacement:="", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

Although you should never use .Select so I would use:

Columns("K:K").Replace What:="''", Replacement:="", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

Clear contents of any cell in column that contain a double quote in the text string

1 Answers1