macro to delete spaces doesn't compile
 

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,

macro to delete spaces doesn't compile
 

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
--
HTH...

Jim Thomlinson


"Janis" wrote:

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,

macro to delete spaces doesn't compile
 

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


"Janis" wrote:

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,

macro to delete spaces doesn't compile
 

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 wrote:

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,

--

Dave Peterson

macro to delete spaces doesn't compile
 

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

"Joel" wrote:

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


"Janis" wrote:

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,

macro to delete spaces doesn't compile
 

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?

Janis wrote:

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

"Joel" wrote:

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


"Janis" wrote:

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,


--

Dave Peterson

macro to delete spaces doesn't compile
 

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

--
HTH...

Jim Thomlinson


"Janis" wrote:

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

"Joel" wrote:

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


"Janis" wrote:

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,

macro to delete spaces doesn't compile
 

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

Jim Thomlinson


"Dave Peterson" wrote:

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 wrote:

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,

--

Dave Peterson

macro to delete spaces doesn't compile
 

I am an Excel MVP.

Jim Thomlinson wrote:

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

Jim Thomlinson

"Dave Peterson" wrote:

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 wrote:

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,

--

Dave Peterson


--

Dave Peterson

macro to delete spaces doesn't compile
 

I didn't see you in the list.
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I am an Excel MVP.

Jim Thomlinson wrote:

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

Jim Thomlinson

"Dave Peterson" wrote:

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 wrote:

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,

--

Dave Peterson


--

Dave Peterson

macro to delete spaces doesn't compile
 

Dave is shy.

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

Gord

On Thu, 26 Jul 2007 11:08:02 -0700, Jim Thomlinson
wrote:

I didn't see you in the list.

macro to delete spaces doesn't compile
 

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.
--
HTH...

Jim Thomlinson


"Gord Dibben" wrote:

Dave is shy.

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

Gord

On Thu, 26 Jul 2007 11:08:02 -0700, Jim Thomlinson
wrote:

I didn't see you in the list.

macro to delete spaces doesn't compile
 

You can see my twin he

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



Jim Thomlinson wrote:

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.
--
HTH...

Jim Thomlinson

"Gord Dibben" wrote:

Dave is shy.

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

Gord

On Thu, 26 Jul 2007 11:08:02 -0700, Jim Thomlinson
wrote:

I didn't see you in the list.



--

Dave Peterson