Excel MS Excel 2010: Any help with the macro/vba?

[Sérgio Santos]

Hello,

In Excel 2007, Having thousands of cells with text having a leading apostrophe, I used this macro to auto remove the apostrophes.

Then when using the same file and macro in Excel 2010, after runing the macro I can't remove the apostrophes at all! It doesn't give any error but it's not working, anyone can tell me why?

Bottom line is: Macro working perfect in vs2007, why not in 2010

I've attatched the file (only the 1st and 2nd "names" contain the apostrophes)

OBS:
- Also tried without sucess 3 or 4 diferent types of macros found in other foruns
- The copy-paste values and add doesn't work and the copy blank cell etc
- In Excel Options/advanced - Transition navigation keys: Disabled
- Also tried saving in compatibility mode 2003-97

The macro:
Sub DeleteApostrophes()
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell

End Sub

... seems that the file doesn't upload to this post, maybe it needs some sort of approval? I'm new here, sorry guys.

 

[alow]

Would you be open to using a formula to accomplish this? You could use the SUBSTITUTE formula to remove the apostrophes and then copy the values back over the original to finish the replacement. Let me know if you're open to the idea, I can explain further. I have no idea why a macro wouldn't work in 2010 when it works in 2007, though.

 

[Sérgio Santos]

Thanks for you reply alow,

I know the this formula SUBSTITUTE(cell;"";"") does the trick, but as I said they are thousands of cells and many updated every hour to the file hosted by intranet server so... using the formula would result in errors if the person doesn't go manualy and every time to time "copy-paste"... the script I've post does the job (with a auto run script), but only in 2007... don't know why and i cant find any help with google...

(Tried again to attatch the file without sucess...)

 

[AmjiBhai]

Hi,

i have modified your macro and it should be helpful i believe,
You need to have a blank sheet , name it , say [Download]
in the end you should have a Download sheet in which you have cells without Apostrophes.


Sub DeleteApostrophes()
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange

sheets("Download").range(rCell.address)=right(rcell,len(rcell))

Next rCell

End Sub