unwanted symbol

I am currently exporting data to an excel worksheet.

One of the data consist an address field which when exported to excel, will be in a multi line order. eg.

vba building
22 new york street
new york 122655

but the problem is that beside name of building, address and postal code will appear a square symbol (i'm guessing its becos of the enter key which allows multiline data). tried to do the replace function to replace the square with a blank, but it didnt work, squares still visible.

anyone know how to make the square box dissapear?

You could use the excel CLEAN function, which will remove all non-printable character.

could you give me an example where can i put the clean function?

VB Code:

Sub CleanExample()
Dim rngAreaToClean As Range
Dim rngMyCell As Range
    
    'Whatever range you need to clean
    Set rngAreaToClean = ThisWorkbook.Worksheets(1).UsedRange
    
    'Loop through each cell
    For Each rngMyCell In rngAreaToClean.Cells
        
        With rngMyCell
            
            'Removing non-prinable characters
            .Value = Application.WorksheetFunction.Clean(.Value)
            
        End With
        
    Next rngMyCell
End Sub

Hi Declan,

Thanks for this Great code!

But, I would like to know what the non-printable characters this code will remove?

I believe that it will remove all nonprintable characters,
LF, CR NBSP, etc

I also encounter the "squares" problem from time to time in Excel 2003 and thought this CLEAN function would be of real use. However, when I applied it, it also brought the second line of any cells containing multiline text back to the first :cry: .

For example, a table header (cell) with the title:

Total
Mass

was changed to:

TotalMass

Yes! it happend to me also. How can we get around of this problem.

Is there any equivalent code in vb6? (not using excel)

A carriage return or Enter key is a non-printable character. So when you use Clean it removes the line breaks and it will all appear on one line.