Macro to replace ONLY x's with blank

[JugglerJAF]

I have a customer data file in which some cells have been completed with x's and I need to replace any cell that contains x's with a blank.

I can easily do this when the cell contains a single x by using find and replace within the macro, but some customers have completed fields with xx or xxx or xxxx and so on - these x's may be in upper or lower case.

I can't just replace all instances of x (or X) with blank on partial cells as there will be company names or address details that will contain an x.

What I need is a way to identify cells that contain ONLY x character(s): so "x" would be replaced with blank as would "xx", "xxx" etc., but "XYZ Limited", "LOXX & Co." or "Essex" need to be left as is.

I can't even think where to begin on this. Anyone got any bright ideas or can point me in the right direction?

Thanks
JAF

 

[xenou]

Sure - find and replace has an option to "match entire cell contents" - that's what you want, I think. Also, just select the column or data area you want to replace in, rather than the whole sheet (to be safe). And pick "Values" as the search option for "Look In".

You can do a case sensitive search too but I don't think that applies here...

ξ

 

[JugglerJAF]

Sure - find and replace has an option to "match entire cell contents" - that's what you want, I think. Also, just select the column or data area you want to replace in, rather than the whole sheet (to be safe). And pick "Values" as the search option for "Look In".

You can do a case sensitive search too but I don't think that applies here...

ξ

Thanks for the response. While I could do a find and replace on entire cell contents, I'd have to do that a whole bunch of times to pick up all the possible permutations of just x's (x, xx, xxx, xxxx, xxxxx, xxxxxx, xxxxxx etc.). The permutations of x in a cell could be anywhere from 1 to 40 and I'm sure there has to be a better way than doing 40 find/replace operations (especially considering that I could have over 20,000 rows of data and need to check each row across 6 columns)!!

What I'm after is a way to identify cells that ONLY contain x's and to replace those with blank, while not changing cells that contain an x along with any other letters, so x (or xx, xxx, xxxx, xxxxx, xxxxxx, xxxxxxx, xxxxxxxx etc.) would be replaced with a blank, but "Fox's Biscuits" would remain unchanged as although it contains an x it contains other letters as well.

 

[VoG]

Try this

Sub DelX()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    With c
        If Replace(LCase(.Value), "x", "") = "" Then .Value = ""
    End With
Next c
End Sub

 

[Jazzed]

Shouldn't that be...?

If WorksheetFunction.Substitute(LCase(.Value), "x", "") = "" Then .Value = ""

 

[VoG]

Why not use the VBA function Replace?

 

[Jazzed]

Why not use the VBA function Replace?

When I run your code, 'Replace' is highlighted and the following message appears...

"Compile Error:

Sub or Function not defined"

 

[VoG]

Which version of Excel do you have?

 

[Jazzed]

Which version of Excel do you have?

I have Excel X for the Mac. I guess that function, along with others, is not available for the Mac version, right?

 

[VoG]

I think you are correct but I have no way of checking. Your version of my code should work in any version of Excel except MAC 2008.