ExcelBanter - Trim Difficulties

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Trim Difficulties (https://www.excelbanter.com/excel-programming/338618-trim-difficulties.html)

WillRn

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

Jim Rech

Trim Difficulties

Dr. Stephan Kassanke

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

Tom Ogilvy

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

RB Smissaert

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

arno

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

WillRn

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

WillRn

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

Tom Ogilvy

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

All times are GMT +1. The time now is 01:27 PM.