Excel spaces

[Closed]
Report
-
 nick -
Hello,
I have lot data in excel
in some of the cells user has done alt enter instead of enter

i have used the following macro which is not working


Sub ascichar()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:B5")
x.Value = =SUBSTITUTE(x.Value,"CHAR10","")
Next
End Sub

6 replies

this macro is not working
it is removing one character when there is no alt+enter space

my file has alt+enter(char10) in some cells but not in all places
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
does it work on cells where there is alt+enter. If so I do not know whether this will help.

Try this macro and see whether you succeed

Sub test()
Dim c As Range
For Each c In ActiveSheet.UsedRange
if c.wraptext=true then 
c = Mid(c, 1, Len(c) - 1)
end if 
Next
End Sub
this code works fine first time and when cells are continuosly filled

exmaple
a1, a2 , a3, a4, a5 is filled fully

say a1,a2, a4,a5 is filled and a3 is blank

i get the following error runtime error 5
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
your original idea using substitute function is generally correct. only this in macros you use
chr(10) and in spreadsheets you use char(10)

see this macro

Sub test()
Dim c As Range, x As String
For Each c In ActiveSheet.UsedRange
c.WrapText = False
x = WorksheetFunction.Substitute(c, Chr(10), "")
c = x
Next c
End Sub


the statment
c.wraptext=false is necessary.
this works well
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
try this macro and tell whether you succeed

Sub test()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c = Mid(c, 1, Len(c) - 1)
Next
End Sub