need to change this to fit all cells in named range "apples".

Sub NoSpaces()
Dim c As Range

For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub

if i understand correctly, you gave yourself the answer

For Each c In Range("apples")

--


Gary Keramidas
Excel 2003


"J.W. Aldridge" wrote in message
...

need to change this to fit all cells in named range "apples".

Sub NoSpaces()
Dim c As Range

For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub

is there a quicker way to execute this?

it's taking almost 5-10 minutes as is.

thanx

Can you just use Edit|Replace to replace all the space characters (match entire
cell, though) with nothing?

Select the range first.

"J.W. Aldridge" wrote:

need to change this to fit all cells in named range "apples".

Sub NoSpaces()
Dim c As Range

For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub

--

Dave Peterson

Unsure if its faster but consider:

Sub test()
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub


Obviously you could speed it even further by changing Cells. to a
specific range.

I wouldn't use xlpart for this.

Youmayfindthatyouendupwithwordsstucktogetherinally ournonemptycells.

But if there's a chance that the original poster has multiple spaces in the cell
and has to get rid of all of those then maybe something like this:

Option Explicit
Sub testme()
Dim myRng As Range
Dim iCtr As Long
Dim MaxSpacesToFix As Long

MaxSpacesToFix = 10

With Worksheets("Sheet1")
Set myRng = .Range("A1").EntireColumn
End With

For iCtr = 1 To MaxSpacesToFix
myRng.Replace what:=Space(iCtr), _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlNext, _
MatchCase:=False
Next iCtr

End Sub

Adjust the maxspacestofix to what you know(?) it can't exceed.

Jef Gorbach wrote:

Unsure if its faster but consider:

Sub test()
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub

Obviously you could speed it even further by changing Cells. to a
specific range.

--

Dave Peterson

try this then:

Sub NoSpaces()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Range("apples")

c = Replace(c, " ", "")
Next

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


--


Gary Keramidas
Excel 2003


"J.W. Aldridge" wrote in message
...
is there a quicker way to execute this?

it's taking almost 5-10 minutes as is.

thanx