Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 796
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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 796
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
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 796
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!