Remove all spaces in Macro

[celia]

Can I change the code below so that it would auto-remove whenever there
are spaces and don't have to run macro everytime? I think can code
somthing to the change event of the worksheet, how to modify?

Sub RemoveAllSpaces()

**Application.ScreenUpdating = False
**Application.Calculation = xlCalculationManual

Range("A14:A50").SpecialCells(xlConstants).Replace
What:=Chr(32), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True

**Application.Calculation = xlCalculationAutomatic
**Application.ScreenUpdating = True
End Sub

what is the meaning of the line of code with **, can someone explain a
bit? Can I exclude those codes?

thanks for any help.

Thanks,
celia

 

[raymondsum]

celia,

if you dont want to use macro, you can try the follwoing

Edit / Goto / Special / Blanks / Edit / Delete / EntireRow

OR

VB:
Selection.SpecialCells(x1CellTypeBlanks).EntireRow.Delete
(P.S. this tips submited by Dave Ramage, UK)

HTH

Raymon

 

[celia]

Raymond,

I think you misunderstood my question. The macro is works to change fo
example "12 3 4" into "1234".
and I want the macro to be modified so that it can automaticall
function when such situation as above happen.

Anyone know the solutions?

Thanks,
celia

 

[Dave Peterson]

The screenupdating bit stops each change to a cell being shown one by one. The
macro does all the work and then just shows the new screen. It stops the
flickering and makes the macro work faster.

The calculation bit tells excel not to worry about recalculating any cell that
it thinks should be recalculated. If you're doing lots of changes, you can just
have the macro do a calculation at the end.

This also saves time (since you don't have to wait for the recalcs).

 

[Dave Peterson]

Right click on the worksheet tab that should have this behavior. Select view
code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim testStr As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a14:a50")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
testStr = Application.Substitute(Target.Value, " ", "")
If IsNumeric(testStr) Then
Application.EnableEvents = False
Target.Value = testStr
End If

errHandler:
Application.EnableEvents = true

End Sub

Modify the range to match where you do your data entry.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm