Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have written a macro to remove special characters in a sheet based on ascii values but the problem with it is that it is replacing the cell content. For example p;j;h which should become p,j,h is becoming ,, (missing the data). Do I need to include any additional statements, or how else to adjust my code?

sub specialcharecters()

Dim i As Long

For i = 32 To 43
    Selection.Replace what:=Chr(i), replacement:=", ", LookAt:=xlPart, SearchOrder:= _
       xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

     END sub
share|improve this question

I stepped through your code and it appears that the culprit is Chr(42), which corresponds to the * symbol. When Excel encounters that, it's treating that as a wildcard and replacing all the text. If you add a simple if statement to skip 42, it gets through the first For loop without causing the observed problems. You'll need to do it again for Chr(63) (the ?) or it will give weird output again. Once both of those are accounted for, it worked just fine for me.

share|improve this answer
Thank you it worked but how do i specify it to run for a particular column? – william Oct 17 '12 at 18:34
The way you have the code set up now, it runs on whatever you have selected. So if you want it run on an entire column, just select that column and then run the code. – techturtle Oct 17 '12 at 18:39
but i did create a button and assigned the macro to it so when the user presses the button it should do the rest. Any suggestions please!! – william Oct 17 '12 at 18:43
Not sure what suggestions you need... If you select Column A and press the button assigned to the macro, it should run your code only on Column A and not anywhere else on the rest of the sheet. It's working that way for me right now. Is yours doing something else? – techturtle Oct 17 '12 at 18:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .