Previous Topic Next Topic

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

Answer 1 / 6

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
Answer 2 / 6
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
Answer 3 / 6

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
Answer 4 / 6
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.
Answer 5 / 6

this works well
Answer 6 / 6
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
Ask a question