lucpian
04-07-2008, 09:31 AM
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
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