Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete spaces | Excel Discussion (Misc queries) | |||
delete spaces | Excel Programming | |||
delete single spaces | Excel Discussion (Misc queries) | |||
DELETE SPACES In part | Excel Programming | |||
delete spaces | Excel Worksheet Functions |