PDA

View Full Version : line breaks & spaces



mikeo1313
04-25-2010, 01:07 PM
I'd like to insert a line break into or replace a certain amount of spaces. I've tried using find & replace and did a couple searches that referenced char(10) as line break but it didn't work. I also tried finding char(9) for tabs but I doubt they make it through excels text import process.

q.1.
I need a macro for replacing for every 10 blank spaces and put a line break in the cells of a whole column or selection in excel?


---====---=-=-=-=-=-=-=----AND


I found this vba for removing spaces in a cell
Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub

q.2.
How can I make it trim for a whole column or selection?


Yes I am new to all this and fiddled a bit to actually get the vb editor up, I appreciate the help.

mdmackillop
04-25-2010, 01:28 PM
Welcome to VBAX

Can you post a sample workbook using Manage Attachments in the Go Advanced reply section.

Regards
MD

Paul_Hossler
04-25-2010, 03:48 PM
Try this. Paste into a regular module (Insert Module from the VBE), not into a worksheet module (also on the VBE, but different)

As it is now, it replaces 10 spaces with a line break and turns on text wrap for the cells or columns that are selected



Option Explicit

Const s10Spaces As String = " "

Sub ReplaceSpaces()

Dim rData As Range, rCell As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rData = Nothing
On Error Resume Next
Set rData = Intersect(Selection, Selection.Parent.UsedRange)
On Error GoTo 0

If rData Is Nothing Then Exit Sub

Application.ScreenUpdating = False

With rData
.Replace What:=s10Spaces, Replacement:=vbLf
.WrapText = True
End With

Application.ScreenUpdating = True
End Sub

Paul

mikeo1313
04-25-2010, 03:56 PM
This is a "macro enabled" file

mikeo1313
04-25-2010, 03:59 PM
this is 2003 excel format

mikeo1313
04-25-2010, 04:14 PM
Try this. Paste into a regular module (Insert Module from the VBE), not into a worksheet module (also on the VBE, but different)

As it is now, it replaces 10 spaces with a line break and turns on text wrap for the cells or columns that are selected



Option Explicit

Const s10Spaces As String = " "

Sub ReplaceSpaces()

Dim rData As Range, rCell As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rData = Nothing
On Error Resume Next
Set rData = Intersect(Selection, Selection.Parent.UsedRange)
On Error GoTo 0

If rData Is Nothing Then Exit Sub

Application.ScreenUpdating = False

With rData
.Replace What:=s10Spaces, Replacement:=vbLf
.WrapText = True
End With

Application.ScreenUpdating = True
End Sub

Paul


Wow, works perfectly!!! I went from this :banghead: to this :p. Thanks Paul!!

Aussiebear
04-25-2010, 09:23 PM
Yes, he does have that effect on people. :devil2: