Macro to remove characters
The formula didnt do anything even though all we did was replace value to
formula. Maybe I am leaving out critical information that I am not aware of,
would someone be willing to look at my Excel Grid that I am trying to fix.
"Gord Dibben" wrote:
Revised.................change Cell.Value to Cell.Formula
Sub ReplaceCR()
Dim rCell As Range
Dim K As Long
Dim NewString As String
For Each rCell In Selection.Cells
For K = 1 To Len(rCell.Value)
If Mid(rCell.Value, K, 1) < Chr(10) Then
NewString = NewString & Mid(rCell.Formula, K, 1)
Else
NewString = NewString & " " 'replacement space
End If
Next K
rCell.Value = NewString
NewString = ""
Next rCell
End Sub
Gord Dibben MS Excel MVP
On Fri, 8 Jun 2007 16:59:02 -0700, YanYan
wrote:
Yes it is CHR10, the character is appearing at the beginning and other times
at the end of my data. This is the macro that I am using to remove it:
Sub ReplaceCR()
Dim rCell As Range
Dim K As Long
Dim NewString As String
For Each rCell In Selection.Cells
For K = 1 To Len(rCell.Value)
If Mid(rCell.Value, K, 1) < Chr(10) Then
NewString = NewString & Mid(rCell.Value, K, 1)
Else
NewString = NewString & " " 'replacement space
End If
Next K
rCell.Value = NewString
NewString = ""
Next rCell
End Sub
This macro removes my formulas.
"JLatham" wrote:
It would help to see the code for the macro you are currently using that is
also destroying your formulas. We could probably fix it easily. I suspect
your macro is simply removing the right-most character from what ever is in
the cell. You need to test the value in a cell before stripping that
character to see that it is CHR(10).
"YanYan" wrote in message
...
I have an Excel Workbook where the sheets are all linked to one main sheet.
The data on the main sheet ends up with character10 (the little square
box)
in each cell.
I have a macro that I run and it deletes all the characters for me, its
great I love it, but it also deletes all the formulas on that sheet which
I
need.
How can I delete that character without deleting my formulas.
|