Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
I am trying to write a code that will remove a blank character in the from
the left side of cells in a particular range. After several permutations and trials, I only get the first cell's value pasted into all the cells in the rage. Here is the lastest code I have tried: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i End Sub I have also tried LTrim with the same results, . . . Any help would be greatly appreciated, Will |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
i.value = trimstring
-- Jim "WillRn" wrote in message ... |I am trying to write a code that will remove a blank character in the from | the left side of cells in a particular range. After several permutations and | trials, I only get the first cell's value pasted into all the cells in the | rage. | | Here is the lastest code I have tried: | | Sub SpaceRemover() | | Dim mystring, trimstring | Dim i As Range | Dim MyRange As Range | Set MyRange = Range("F56:F132") | | For Each i In MyRange | | mystring = ActiveCell | trimstring = Trim(mystring) | MyRange = trimstring | | Next i | | End Sub | | | I have also tried LTrim with the same results, . . . | | Any help would be greatly appreciated, | | Will |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
"WillRn" schrieb im Newsbeitrag ... I am trying to write a code that will remove a blank character in the from the left side of cells in a particular range. After several permutations and trials, I only get the first cell's value pasted into all the cells in the rage. Here is the lastest code I have tried: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i End Sub I have also tried LTrim with the same results, . . . Any help would be greatly appreciated, Will Hi Will, replace the following in the loop: For Each i In MyRange mystring = i ' get current cell value trimstring = Trim(mystring) ' trim value i = trimstring ' write back to current cell Next i cheers, Stephan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
Dim mystring, trimstring
Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = i.value trimstring = Trim(mystring) MyRange = trimstring Next i End Sub -- Regards, Tom Ogilvy "WillRn" wrote in message ... I am trying to write a code that will remove a blank character in the from the left side of cells in a particular range. After several permutations and trials, I only get the first cell's value pasted into all the cells in the rage. Here is the lastest code I have tried: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i End Sub I have also tried LTrim with the same results, . . . Any help would be greatly appreciated, Will |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
This should do it if you alter your macro:
Sub SpaceRemover() Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange.Cells i = Trim(i) Next i End Sub I think something like this will be faster though: Sub test() Dim arr Dim i As Long arr = Range("F56:F132") For i = 1 To UBound(arr) arr(i, 1) = Trim(arr(i, 1)) Next Range("F56:F132") = arr End Sub RBS "WillRn" wrote in message ... I am trying to write a code that will remove a blank character in the from the left side of cells in a particular range. After several permutations and trials, I only get the first cell's value pasted into all the cells in the rage. Here is the lastest code I have tried: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i End Sub I have also tried LTrim with the same results, . . . Any help would be greatly appreciated, Will |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
Hi Will,
For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i for each i in myrange i = trim(i) next arno |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
Sorry,
Still getting the same result with: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("E8:E79") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) i.Value = trimstring Next i End Sub "Jim Rech" wrote: i.value = trimstring -- Jim "WillRn" wrote in message ... |I am trying to write a code that will remove a blank character in the from | the left side of cells in a particular range. After several permutations and | trials, I only get the first cell's value pasted into all the cells in the | rage. | | Here is the lastest code I have tried: | | Sub SpaceRemover() | | Dim mystring, trimstring | Dim i As Range | Dim MyRange As Range | Set MyRange = Range("F56:F132") | | For Each i In MyRange | | mystring = ActiveCell | trimstring = Trim(mystring) | MyRange = trimstring | | Next i | | End Sub | | | I have also tried LTrim with the same results, . . . | | Any help would be greatly appreciated, | | Will |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
Thanks!,
This last one worked. Will "RB Smissaert" wrote: This should do it if you alter your macro: Sub SpaceRemover() Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange.Cells i = Trim(i) Next i End Sub I think something like this will be faster though: Sub test() Dim arr Dim i As Long arr = Range("F56:F132") For i = 1 To UBound(arr) arr(i, 1) = Trim(arr(i, 1)) Next Range("F56:F132") = arr End Sub RBS "WillRn" wrote in message ... I am trying to write a code that will remove a blank character in the from the left side of cells in a particular range. After several permutations and trials, I only get the first cell's value pasted into all the cells in the rage. Here is the lastest code I have tried: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i End Sub I have also tried LTrim with the same results, . . . Any help would be greatly appreciated, Will |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim Difficulties
one more change required:
Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = i.value trimstring = Trim(mystring) i = trimstring Next i End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = i.value trimstring = Trim(mystring) MyRange = trimstring Next i End Sub -- Regards, Tom Ogilvy "WillRn" wrote in message ... I am trying to write a code that will remove a blank character in the from the left side of cells in a particular range. After several permutations and trials, I only get the first cell's value pasted into all the cells in the rage. Here is the lastest code I have tried: Sub SpaceRemover() Dim mystring, trimstring Dim i As Range Dim MyRange As Range Set MyRange = Range("F56:F132") For Each i In MyRange mystring = ActiveCell trimstring = Trim(mystring) MyRange = trimstring Next i End Sub I have also tried LTrim with the same results, . . . Any help would be greatly appreciated, Will |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Difficulties | Excel Worksheet Functions | |||
difficulties | Excel Discussion (Misc queries) | |||
Difficulties with COUNTIF. | Excel Worksheet Functions | |||
VBA Trim and Application.worksheetfunction.Trim | Excel Programming | |||
Trim like worksheet Trim | Excel Programming |