Archive of Mr Excel Message Board

One way to attack that is to highlight your range, and click Edit > Replace. In the "Find what" field, hit your spacebar once, leave the "Replace with" field totally alone, choose Search by columns, and click the Replace All button.

Tom Urtis

Tom,
it is a good idea
I recorded the macro the way u outlined it is something like this

Sub Macro1()
Columns("A:A).Select
Selection.Replace What:=" ", Replacement:="",_
LookAt:xlPart, SearchOrde:=xlByColumns,_
MatchCase:False
End Sub

Howcan you make this sub to replace any cell which has more than 1 SPACE character in it ?
i.e. " " or " " or " ", etc.

thanks

-alex

Re-state the problem: replacing any cells that have one or more " " and no other text characters

I tried this code under different spacebar circumstances and it seems to work the way I think you want it to:

Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False

One other thought, if you have data in a cell that starts with or ends with space bars, you can use the TRIM function to remedy that:
=TRIM(A2)
and copy down as needed.

If there is still a problem, please repost.

Tom Urtis

Re-state the problem: replacing any cells that have one or more " " and no other text characters

Tom,

This macro works fine with the cells one or more " " only but if the column also includes entires like "first second", this macro converts it to "firstsecond"

how do you get around this ?

thanks
-alex I tried this code under different spacebar circumstances and it seems to work the way I think you want it to: Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, _

Try this macro that cleeans unwated chr and
trims the spaces

Sub Macro1()
Dim CTRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CTRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CTRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next

End Sub

Ivan Tom, This macro works fine with the cells one or more " " only but if the column also includes entires like "first second", this macro converts it to "firstsecond" how do you get around this ?

Ivan,
It is such a great method...but when i tried to trigger this by Change event it doesnt work, it stalls, Id like this macro run when a user enters a new value or edit an existing entry in the column column range of C2:C100.

PS.the code below works fine when i tied to a commandbox.

Im sorry if my questions sound stupid but its been only 2 weeks since ive started working with VBA.

Here is version of the code i tried to implement:
-----------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim CTRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction
Dim myRange As Range

Set Func = Application.WorksheetFunction

Set myRange = Worksheets("Sheet1").Range("C2:C100")

On Error Resume Next

Set CTRg = myRange.SpecialCells(xlCellTypeConstants, 2)

If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CTRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next
End Sub
-----------------

Try this macro that cleeans unwated chr and trims the spaces Dim CTRg As Range Dim oCell As Range Dim Func As WorksheetFunction Set Func = Application.WorksheetFunction On Error Resume Next Set CTRg = Selection.SpecialCells(xlCellTypeConstants, 2) If Err Then MsgBox "No data to clean and Trim!": Exit Sub For Each oCell In CTRg oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell)) Next Ivan : Tom, : This macro works fine with the cells one or more " " only but if the column also includes entires like "first second", this macro converts it to "firstsecond" : how do you get around this ? : -alex

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim CTRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction
Dim myRange As Range
Dim WatchRg As Range

Set Func = Application.WorksheetFunction

Set myRange = Range("C2:C100")

On Error Resume Next

Set CTRg = myRange.SpecialCells(xlCellTypeConstants, 2)
If Err Then Exit Sub

Set WatchRg = Application.Intersect(Target, CTRg)
If WatchRg Is Nothing Then Exit Sub
On Error GoTo 0
Application.EnableEvents = False
For Each oCell In CTRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next
Application.EnableEvents = True

End Sub

Ivan, It is such a great method...but when i tried to trigger this by Change event it doesnt work, it stalls, Id like this macro run when a user enters a new value or edit an existing entry in the column column range of C2:C100. PS.the code below works fine when i tied to a commandbox. Im sorry if my questions sound stupid but its been only 2 weeks since ive started working with VBA. Here is version of the code i tried to implement: ----------------------------------------- Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim CTRg As Range Dim oCell As Range Dim Func As WorksheetFunction Dim myRange As Range Set Func = Application.WorksheetFunction Set myRange = Worksheets("Sheet1").Range("C2:C100") On Error Resume Next Set CTRg = myRange.SpecialCells(xlCellTypeConstants, 2) If Err Then MsgBox "No data to clean and Trim!": Exit Sub For Each oCell In CTRg oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell)) Next End Sub ----------------- : Try this macro that cleeans unwated chr and : trims the spaces : Sub Macro1() : Dim CTRg As Range : Dim oCell As Range : Dim Func As WorksheetFunction : Set Func = Application.WorksheetFunction : On Error Resume Next : Set CTRg = Selection.SpecialCells(xlCellTypeConstants, 2) : If Err Then MsgBox "No data to clean and Trim!": Exit Sub : For Each oCell In CTRg : oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell)) : Next : : End Sub : : Ivan

Ivan, you know this stuff man ! Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim CTRg As Range Dim oCell As Range Dim Func As WorksheetFunction Dim myRange As Range Dim WatchRg As Range Set Func = Application.WorksheetFunction Set myRange = Range("C2:C100") On Error Resume Next Set CTRg = myRange.SpecialCells(xlCellTypeConstants, 2) If Err Then Exit Sub Set WatchRg = Application.Intersect(Target, CTRg) If WatchRg Is Nothing Then Exit Sub On Error GoTo 0 Application.EnableEvents = False For Each oCell In CTRg oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell)) Next Application.EnableEvents = True End Sub : It is such a great method...but when i tried to trigger this by Change event it doesnt work, it stalls, Id like this macro run when a user enters a new value or edit an existing entry in the column column range of C2:C100. : PS.the code below works fine when i tied to a commandbox. : Im sorry if my questions sound stupid but its been only 2 weeks since ive started working with VBA. : Here is version of the code i tried to implement

Alex
This macro seems to work for me. Hope it helps you

On Error Resume Next
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each cell In Selection
If cell.Value <= " " Then cell.ClearContents
Next
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Derek

Alex
This macro seems to work for me. Hope it helps you

On Error Resume Next
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each cell In Selection
If cell.Value <= " " Then cell.ClearContents
Next
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Derek