How to globally turn all zero-length strings into empty cells? 1

[iyarosh]

Hello,

My Excel application obtains the Excel file that is created by a trird party tool and if there is no data to be placed in cell the tool puts zero-length string in it making it impossible to use any math functions unless the string is removed. To resolve the issue I'm trying to replace all zero-length string cells with empty ones. I've tried number of approaches but have not come with acceptable/workable solution yet. One way that I made it work was to go trhough every cell in selection and check the lenght, and if the cell has zero-length string in clear it using ClearContents method, but that technique made my application painfully slow. Another approach would be to select all zero-length strings on the worksheet at once and replace them with empty cells, but I do not know how to select zero-length string cells in the document. I also thought about using find method, but, again, did not know how to input zero-length string and empty string as the parameters to the method. Any help would be highly appreciated.

Thanks,
Igor.

 

[AnotherHiggins]

Try this:

range("b:b").value = range("b:b").value

or

Selection.value = selection.value

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

I think in this instance, your best bet would be to multiply the entire worksheet by 1

Enter a 1 into any blank cell
Copy it
Edit>Paste Special choose "Values" & "Multiply"

et voila (hopefully)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[iyarosh]

John,
Thank you for simple and effective solution.

Geoff,
Thank you for looking into it.

Best Regards,
Igor.