macro to delete spaces doesn't compile

[Janis]

This macro has an error and doesn't compile. I also need it to delete 2
spaces.


Sub delSpaces()

Intersect(Selection, Selection.SpecialCells(xlConstants,
xlTextValues)).replace(", ",",")

End Sub

tia,

 

[Jim Thomlinson]

If you do not have any constants then you will have an error. Also you don't
need the intersect. So something like this...

Sub delSpaces()
On Error Resume Next
Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Replace ", ",
","
On Error GoTo 0
End Sub

 

[Joel]

I don't know why you have an intersection, but this code runs

Sub delSpaces()
Set c = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
c.Replace What:=", ", Replacement:=","

End Sub

 

[Dave Peterson]

Be careful.

You may want the Intersect() still. If your selection is a single cell, then
the intersect() will mean that you're only working on that single cell.

Without it, the whole worksheet may be affected.

But you don't need those () after the .replace:

Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues)) _
.Replace ", ", ","

But if you want to remove 2 spaces, don't you want to specify 2 spaces in the
From portion???

Or run it multiple times:

Option Explicit
Sub delSpaces()

Dim iCtr As Long

For iCtr = 1 To 2
Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues)) _
.Replace ", ", ","
Next iCtr
End Sub

 

[Janis]

I got the one with the selection to work for now but for the user I would
like to have the workbook and the column so I tried this and something is
wrong with the object path for C.


Sub cleanText()
Dim C As Range
On Error Resume Next
With ActiveWorkbook
Set C = Worksheets("Port History).Columns(L)
C.Replace What:=", ", Replacement:=",", SearchOrder:=xlByColumns

On Error GoTo 0
End With
End Sub

thanks

 

[Dave Peterson]

Set C = Worksheets("Port History).Columns("L")
or
Set C = Worksheets("Port History).Range("L1").entirecolumn
or
Set C = Worksheets("Port History).range("l:l")

Or was L some sort of variable--not the column letter?

 

[Jim Thomlinson]

You need to watch the quotation marks...

Sub CleanText()
On Error Resume Next
Worksheets("Port History").Columns("L").Replace What:=", ", Replacement:=","
On Error GoTo 0
End With
End Sub

 

[Jim Thomlinson]

Good point... How is it you are not an MVP yet? I'd vote for you.

 

[Dave Peterson]

I am an Excel MVP.

Good point... How is it you are not an MVP yet? I'd vote for you.

 

[Jim Thomlinson]

I didn't see you in the list.

 

[Gord Dibben]

Dave is shy.

We would all like to see a photo and a profile but nothing yet.

Gord

 

[Jim Thomlinson]

Come on Dave... Don't you want to be famous. Get recognized on the street.
Throngs of start struck adoring fans. Instant access to all the hottest clubs.

 

[Dave Peterson]

You can see my twin here:

http://www.cinema.com/image_lib/news_georgeclooney5.jpg