delete only black text from cells containing both blue & black text

Hello there,

I have a spreadsheet with text in two colors, often in the same cells. I would like to run a macro which deletes only the black text from each cell in which it appears. Thanks for any help you can give!

maybe something like this

Please Login or Register  to view this content.

Hi Nilem,

Thanks for this. Unfortunately the macro threw a run-time error '1004': Delete method of characters class failed. So the last instruction just needs to be tweaked a little, hopefully? Thanks again, I am hugely grateful for your help.

I have this code works. Here is a sample file, try to select a range of F1:F10 and press the green button.

Hi Nilem,

Thank you very much for this, I can see the code works in your spreadsheet, how exciting.

Unfortunately, I'm still not able to get it to work, though. Let me tell you what I'm doing. I have copied 'ert' into my workbook via ALT+F11 and 'insert module' and run it and then the error code pops up. So I saved the file as .xlsm and tried again, still no luck. Then I took the hundreds of cells from my workbook and copied them into yours to see if I could delete the text with your green button - the same error pops up even in your workbook. What am I doing wrong?

Many thanks for your patience with me.

All right. Show your file with your data, where the error occurs. Hopefully we'll figure something out.

YES! I went to use a library coy of Excel 2007 (I was using 2010) and it WORKS!

THANK YOU, NILEM!

It's amazing just to be able to click and have the spreadsheet exactly how I wanted it to be

oh, drat! It worked for the first test section and then stopped working with the 1004 error message! SO CLOSE. I have attached the file to show you what I mean. I copied and pasted the text that needs to be 'cleaned' under your text in the spreadsheet you sent (so the data is anonymised), grateful for any advice as to how to make it work.

Maybe this option is suitable for you.
Black characters are removed, and the colored characters are in the cell, but lose their color.
Use the purple button.

If I may chime in, perhaps the problem is that the black text isn't actually black (per the programming side). This can be an issue if it's copied and pasted over from another application.

Maybe run a quick test to see what the color actually is. To do so, select a cell wherein the first letter is black. If necessary, manually delete any other text in the cell so that the first letter is black. If your IMMEDIATE window is not open, press CTRL+G to open it. Then, paste the following code into a standard module:

Please Login or Register  to view this content.

This should report in the standard module what VBA THINKS the black color actually is. This may help with the coding.

YAY! I was able to use the purple button to remove ALL of the unwanted text! Thank you, Nilem! And thank you BigBas for your message, too!

Very best to both, F.

@ london.cl.2012

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.