Code to delete a single character from a single cell

Hi,

As a final touch for a macro (which I already have), I'd like to delete a single character from cell A2, and leave a blank space, but the code I'm using does nothing.

The content of the cell might have one or 2 instances of a dash (-), like this: ABC-123 or AB-C-123. I need it like this: ABC 123 or AB C 123

This is the line I have: Range("A2").Characters(45).Delete

What am I missing? Is there a better approach to this?

Thanks for any help!

Please Login or Register  to view this content.

Hi Cytop,

Thank you so much for your help. I tried the code, however, Excel gives me a Compile error message as follows: "Wrong number of arguments or invalid property assignment", and it highlights the word "Replace". For testing purposes, I'm running the code on its own (no other code involved). Not sure what's going on?

As far as I can see, on a phone, the syntax is correct. About the only possible issue is if the cell is blank but I can't test at the moment.

Have a read of https://blog.udemy.com/excel-vba-replace/

cytop's code works for me. Maybe try:

Please Login or Register  to view this content.

This should work also:

Please Login or Register  to view this content.

Note: Chr(45) instead of Characters(45) in post #1. Although your line would create an error anyway.

Second thoughts on the compiler error. The code broke over 2 lines (Wasn't me, Guv. Honest)

If you copied it literally then the compiler will complain. Make all on 1 line.

Hi all,

Well, I guess this is one of those cases where VBA has made tiny changes somewhere resulting in non-working code. These are the results of my tests.

I kept trying cytop code using Excel 2016 with no success. I changed the target cell and even re-started Excel and ultimately my computer. Nothing. I later thought trying it with Excel 2010. Much to my surprise, it worked with no issues.

John's code (both options) worked on both versions of Excel.

I'm not exactly knowledgeable when it comes to VBA, but I'm certainly surprised about this issue.

Thanks cytop and John for your help. Truly appreciate it!

Forgot to mention: I'll try all codes with Excel 2013 tomorrow at work, and I'll add comments about this issue then.

You're welcome. Glad to help out and thanks for the feedback.

Works for me on 2016/2013/2010/2007 and even 2003 & 2002 (Office XP)... and there's no issue if the cell is blank.

Hi,

Well, I tried using Excel 2013 and it worked, but 2016 still gives me an error message. Not sure what's going on here. In all instances, I copied and pasted the code directly into the editor, but only 2016 results in an error.

In any case, I'm marking this thread as solved, since I got a working code for my need.

Thank you Cytop and John for your help!