delete all spaces in range

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

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")
 
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.
 
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.

Youmayfindthatyouendupwithwordsstucktogetherinallyournonemptycells.

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.
 
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
 
Back
Top