excel spacesClosed

Question

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

venkat1926 · Answer

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

nick · Answer

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

venkat1926 · Answer

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

nick · Answer

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

venkat1926 · Answer

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.

nick · Answer

this works well

Excel spaces

6 responses

Similar discussions

Subscribe To Our Newsletter!