Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete ghost spaces
Got this code....
Doesnt always work for some reason. Any alternate way to put this? (Or is something wrong with it?) Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c = Replace(c, " ", "") Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete ghost spaces
I'm going to guess some of your spaces are non-breaking spaces (usually
acquired from webpage text), so try using this line of code inside your loop (instead of the one you posted) and see if it works for you... c = Replace(Replace(c, " ", ""), Chr(160), "") -- Rick (MVP - Excel) "J.W. Aldridge" wrote in message ... Got this code.... Doesnt always work for some reason. Any alternate way to put this? (Or is something wrong with it?) Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c = Replace(c, " ", "") Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete ghost spaces
Hi,
maybe they aren't spaces, try this. Your function would also remove internal spaces so if that's what you want put it into the code c.Value = Replace(c.Value, " ", "") Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c.Value = WorksheetFunction.Trim(c.Value) c.Value = WorksheetFunction.Clean(c.Value) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "J.W. Aldridge" wrote: Got this code.... Doesnt always work for some reason. Any alternate way to put this? (Or is something wrong with it?) Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c = Replace(c, " ", "") Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete ghost spaces
"Mike H" wrote:
c.Value = WorksheetFunction.Trim(c.Value) c.Value = WorksheetFunction.Clean(c.Value) The CLEAN function removes only the first 32 nonprinting characters (codes 0-31). It might be prudent to use CLEAN. But I suspect, as Rick does, that the culprit is the so-called nonbreaking space (code 160) that frequently arises when pulling data from web pages. That requires the use of some replacement operation, such as Excel SUBSTITUTE and VBA Replace. In the Unicode character, there are additional nonprinting characters among codes 128-255. See the article at http://office.microsoft.com/en-us/ex...561311033.aspx . There is probably some regular expression method that would replace all of these nonprinting characters in a single pass. I cannot take the time to look into that myself right now. Also note that Excel TRIM reduces multiple interstitial spaces to one space. It does not remove all interstitial spaces, as JW's Replace function does. Only JW can decide which is the correct operation for his/her purposes. ----- original message ----- "Mike H" wrote: Hi, maybe they aren't spaces, try this. Your function would also remove internal spaces so if that's what you want put it into the code c.Value = Replace(c.Value, " ", "") Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c.Value = WorksheetFunction.Trim(c.Value) c.Value = WorksheetFunction.Clean(c.Value) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "J.W. Aldridge" wrote: Got this code.... Doesnt always work for some reason. Any alternate way to put this? (Or is something wrong with it?) Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c = Replace(c, " ", "") Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete ghost spaces
Try one of these:
Sub Remove_CR_LF() With Selection ..Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub Sub Remove_CR_LF() With Selection ..Replace What:=Chr(39), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False ..Replace What:=Chr(180), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End With End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joe User" wrote: "Mike H" wrote: c.Value = WorksheetFunction.Trim(c.Value) c.Value = WorksheetFunction.Clean(c.Value) The CLEAN function removes only the first 32 nonprinting characters (codes 0-31). It might be prudent to use CLEAN. But I suspect, as Rick does, that the culprit is the so-called nonbreaking space (code 160) that frequently arises when pulling data from web pages. That requires the use of some replacement operation, such as Excel SUBSTITUTE and VBA Replace. In the Unicode character, there are additional nonprinting characters among codes 128-255. See the article at http://office.microsoft.com/en-us/ex...561311033.aspx . There is probably some regular expression method that would replace all of these nonprinting characters in a single pass. I cannot take the time to look into that myself right now. Also note that Excel TRIM reduces multiple interstitial spaces to one space. It does not remove all interstitial spaces, as JW's Replace function does. Only JW can decide which is the correct operation for his/her purposes. ----- original message ----- "Mike H" wrote: Hi, maybe they aren't spaces, try this. Your function would also remove internal spaces so if that's what you want put it into the code c.Value = Replace(c.Value, " ", "") Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c.Value = WorksheetFunction.Trim(c.Value) c.Value = WorksheetFunction.Clean(c.Value) Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "J.W. Aldridge" wrote: Got this code.... Doesnt always work for some reason. Any alternate way to put this? (Or is something wrong with it?) Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c = Replace(c, " ", "") Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete ghost spaces
thanx!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete spaces | Excel Discussion (Misc queries) | |||
delete spaces | Excel Programming | |||
how do I delete the last two spaces in a cell | Excel Discussion (Misc queries) | |||
Ho to Delete "Ghost" Pivot Tables | Excel Discussion (Misc queries) | |||
delete spaces | Excel Worksheet Functions |