I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!

Anything wrong with the Substitute function for this cause?

=SUBSTITUTE(A1," ","") where A1 is the cell location of the text.

"a b c" becomes "abc".
--
Y


"Jerry" wrote:

I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!

Try this macro:

Sub RemSpace()

Dim cell As Range
For Each cell In Selection
Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next cell

End Sub

Select the range of cells in question then run the macro.

You really don't need a macro to do this. You can do the same by following
these steps:

Select the range of cells in question
Goto the menu EditReplace
Find what: enter a space in the box by hitting your space bar
Replace with: nothing, leave this empty
Replace all
Close

Biff

"Jerry" wrote in message
ups.com...
I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!

It's easy to get the VBA code by using Tools Macro Record New Macro
and then using the EditReplace method.

BTW The "for each cell" loop is redundant in the code above.

T. Valko wrote:

Try this macro:

Sub RemSpace()

Dim cell As Range
For Each cell In Selection
Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next cell

End Sub

Select the range of cells in question then run the macro.

You really don't need a macro to do this. You can do the same by following
these steps:

Select the range of cells in question
Goto the menu EditReplace
Find what: enter a space in the box by hitting your space bar
Replace with: nothing, leave this empty
Replace all
Close

Biff

"Jerry" wrote in message
ups.com...
I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!

BTW The "for each cell" loop is redundant in the code above.

That's why I almost never post code! Good pointer, though.

Biff

"Lori" wrote in message
oups.com...
It's easy to get the VBA code by using Tools Macro Record New Macro
and then using the EditReplace method.

BTW The "for each cell" loop is redundant in the code above.

T. Valko wrote:

Try this macro:

Sub RemSpace()

Dim cell As Range
For Each cell In Selection
Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next cell

End Sub

Select the range of cells in question then run the macro.

You really don't need a macro to do this. You can do the same by
following
these steps:

Select the range of cells in question
Goto the menu EditReplace
Find what: enter a space in the box by hitting your space bar
Replace with: nothing, leave this empty
Replace all
Close

Biff

"Jerry" wrote in message
ups.com...
I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!

Another option would be to just edit|Replace the space character with nothing:

In code:

Option Explicit
Sub testme01()
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Select the range to fix first.

"T. Valko" wrote:

Try this macro:

Sub RemSpace()

Dim cell As Range
For Each cell In Selection
Selection.Replace What:=" ", Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next cell

End Sub

Select the range of cells in question then run the macro.

You really don't need a macro to do this. You can do the same by following
these steps:

Select the range of cells in question
Goto the menu EditReplace
Find what: enter a space in the box by hitting your space bar
Replace with: nothing, leave this empty
Replace all
Close

Biff

"Jerry" wrote in message
ups.com...
I want to do it automatically for a group of cells.

for example, if the word in a cell is "a b c", after spaces are
removed, the new word in the cell will be "abc"

how to do it by using VBA? thanks!


--

Dave Peterson