Trim Difficulties

[WillRn]

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

 

[Jim Rech]

i.value = trimstring

--
Jim
|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

 

[Dr. Stephan Kassanke]

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

 

[Tom Ogilvy]

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

 

[RB Smissaert]

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

 

[arno]

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

 

[WillRn]

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

 

[WillRn]

Thanks!,

This last one worked.

Will

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

 

[Tom Ogilvy]

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