line breaks & spaces

[mikeo1313]

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?
[FONT=verdana,geneva,lucida,'lucida grande',arial,helvetica,sans-serif]

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

[mdmackillop]

Welcome to VBAX

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

Regards
MD

[Paul_Hossler]

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


[VBA]
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
[/VBA]

[mikeo1313]

This is a "macro enabled" file

[mikeo1313]

this is 2003 excel format

[mikeo1313]

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


[vba]
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
[/vba]

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

[Aussiebear]

Yes, he does have that effect on people.