Clearing extra characters from imported data?

[lucpian]

Hi All,

Good day. I am working on importing text files into Excel which the code I have works out fine, but it imports this extra characters of "" into the empty cells which were also part of the text files. I wrote the following code to loop through the sheet in excell to claer it, but it is not working. Please, members of this forum, what is wrong with my code. Here is the code.


Sub ClearunwantedxtersButton()
Dim strVal As String
Dim rRng As Range
Dim Cols As Integer
Dim Rows As Integer
Dim c As Integer
Dim R As Integer
For i = 1 To 200
strVal = Sheet1.Cells(i, 1).Value
If strVal = "" Then
Cols = i
Exit For
End If
Next

'MsgBox ("cols=" & i)
'
For j = 3 To 65536
strVal = Sheet1.Cells(j, 1).Value
If strVal = "" Then
Rows = j
Exit For
End If
Next

'MsgBox ("rows=" & j)
For R = 1 To Rows - 1
For c = 1 To Cols - 1
If Sheet1.Cells(R, c).Value = """" Then
Sheet1.Cells(R, c).Value = " "

End If


Next
Next
End Sub

Thanks

Lucpian

[MikeO]

Sub ClearunwantedxtersButton()
Dim rRng As Range

For Each rRng In ActiveSheet.UsedRange.Cells
If rRng.Value = """" Then rRng.Clear
Next rRng
End Sub

[mdmackillop]

Check out the Clean method. In addition to those listed, I've come across Char(160) as a space characted in imported data.

From VBA Help

Use Clean on text imported from other applications that contains characters that may not print with your operating system. For example, you can use Clean to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
Important The Clean function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the Clean function does not remove these additional nonprinting characters. For an example of how to remove these additional nonprinting characters from text, see Remove spaces and nonprinting characters from text (mk:@MSITStore:::/html/xlRemoveSpaces_HP10062743.xml).