CODEWITHSUNDEEP
vbaexcel
Pasha
Pasha

Reputation: 181

How to replace all spaces with new lines?

I have a table that contains spaces in its headers 

First Name   Last Name       Average Level Degree   
_________________________________________________
Mike         Lowel           25 
Stan         Wilson          35 
Papa         Peterson        15

I need it to look like this 

 First        Last            Average  
 Name         Name            Level 
                              Degree
 _____________________________________________
 Mike         Lowel           25 
 Stan         Wilson          35 
 Papa         Peterson        15

Here is what I tried 

Sub test()

myString = ThisWorkbook.Worksheets(1).Range("a1").Value
ThisWorkbook.Worksheets(1).Range("a1").Value = Replace(myString, " ", CHAR(10))
ThisWorkbook.Worksheets(1).Range("a1").WrapText = True
ThisWorkbook.Worksheets(1).Range("a1").EntireRow.AutoFit

myString = ThisWorkbook.Worksheets(1).Range("b1").Value
ThisWorkbook.Worksheets(1).Range("b1").Value = Replace(myString, " ", CHAR(10))
ThisWorkbook.Worksheets(1).Range("b1").WrapText = True
ThisWorkbook.Worksheets(1).Range("b1").EntireRow.AutoFit

End Sub

However, it throws an error. In addition, I am not sure how to loop over all letters. Is there any more efficient way. I need those headers to look nice: I need to get the same effect as if I click Alt+Enter. Each word should appear on a separate line

Upvotes: 0

Views: 55

Answers (1)

Dan Donoghue
Dan Donoghue

Reputation: 6216

You can do this with one line of code:

Range("A1:C1").Replace " ",vblf

In the range it replaces all spaces with a VB Line Feed (Return)

Upvotes: 1

Related Questions