trim a range object
 

I have a nice range defined and I want to trim up all the cells.
Can't I do something like


Application.WorksheetFunction.Trim(rng)
or
Application.WorksheetFunction.Trim(rng.Value2);
or
....

I can build a loop, but geezzz, these range objects are too nice to
have to fool around like that. Is there an enumerator somewhere.

Thanks

trim a range object
 

hi
the trim function trims leading and trailing spaces from a string.
a range object requires a defined address such as A1 or A1:D5 or VB
regurgitates
error messages. i have never heard of anyone trying to trim rannge objects
and i doubt that it can be done.
what are you trying to do?

Regards
FSt1

"cate" wrote:

I have a nice range defined and I want to trim up all the cells.
Can't I do something like


Application.WorksheetFunction.Trim(rng)
or
Application.WorksheetFunction.Trim(rng.Value2);
or
....

I can build a loop, but geezzz, these range objects are too nice to
have to fool around like that. Is there an enumerator somewhere.

Thanks
.

trim a range object
 

hi
afterthought.
are you trying to trim values within the range object?

regards
FSt1

"FSt1" wrote:

hi
the trim function trims leading and trailing spaces from a string.
a range object requires a defined address such as A1 or A1:D5 or VB
regurgitates
error messages. i have never heard of anyone trying to trim rannge objects
and i doubt that it can be done.
what are you trying to do?

Regards
FSt1

"cate" wrote:

I have a nice range defined and I want to trim up all the cells.
Can't I do something like


Application.WorksheetFunction.Trim(rng)
or
Application.WorksheetFunction.Trim(rng.Value2);
or
....

I can build a loop, but geezzz, these range objects are too nice to
have to fool around like that. Is there an enumerator somewhere.

Thanks
.

trim a range object
 

Sub trimrng()
Dim r As Range
Set r = [A1]
r.Value = Trim(r.Value)
End Sub

regards
FSt1

"FSt1" wrote:

hi
afterthought.
are you trying to trim values within the range object?

regards
FSt1

"FSt1" wrote:

hi
the trim function trims leading and trailing spaces from a string.
a range object requires a defined address such as A1 or A1:D5 or VB
regurgitates
error messages. i have never heard of anyone trying to trim rannge objects
and i doubt that it can be done.
what are you trying to do?

Regards
FSt1

"cate" wrote:

I have a nice range defined and I want to trim up all the cells.
Can't I do something like


Application.WorksheetFunction.Trim(rng)
or
Application.WorksheetFunction.Trim(rng.Value2);
or
....

I can build a loop, but geezzz, these range objects are too nice to
have to fool around like that. Is there an enumerator somewhere.

Thanks
.

trim a range object
 

Sub trimrng()
Dim r As Range
Dim lr As Long
lr = Cells(Rows.Count, "a").End(xlUp).Row
Set r = Range("A1:A" & lr)
For Each c In r
c.Value = Trim(c.Value)
Next c
End Sub

regard
FSt1

"FSt1" wrote:

Sub trimrng()
Dim r As Range
Set r = [A1]
r.Value = Trim(r.Value)
End Sub

regards
FSt1

"FSt1" wrote:

hi
afterthought.
are you trying to trim values within the range object?

regards
FSt1

"FSt1" wrote:

hi
the trim function trims leading and trailing spaces from a string.
a range object requires a defined address such as A1 or A1:D5 or VB
regurgitates
error messages. i have never heard of anyone trying to trim rannge objects
and i doubt that it can be done.
what are you trying to do?

Regards
FSt1

"cate" wrote:

I have a nice range defined and I want to trim up all the cells.
Can't I do something like


Application.WorksheetFunction.Trim(rng)
or
Application.WorksheetFunction.Trim(rng.Value2);
or
....

I can build a loop, but geezzz, these range objects are too nice to
have to fool around like that. Is there an enumerator somewhere.

Thanks
.

trim a range object
 

There is an enumerator ... see the 4th post by FSt1 on how to implement it.

When you initially set up your large, multi-cell range with something like:
Set rng = Worksheets("Sheet1").Range("A1:Z44")
later on Excel is smart enough to know that when you use the
For Each command, that "Each" refers to an individual cell (also a range
object).

Specifically, your Dim Statements would be like this:
Dim rng As Range
dim anyCell as Range

Then code goes on like FSt1 showed. But FSt1 didn't show the definition of
'c' in the code, to be more complete/proper it should look something like:

Sub trimrng()
Dim r As Range
Dim c As Range 'added for clarity
Dim lr As Long
lr = Cells(Rows.Count, "a").End(xlUp).Row
Set r = Range("A1:A" & lr)
For Each c In r
c.Value = Trim(c.Value)
Next c
End Sub


"cate" wrote:

I have a nice range defined and I want to trim up all the cells.
Can't I do something like


Application.WorksheetFunction.Trim(rng)
or
Application.WorksheetFunction.Trim(rng.Value2);
or
....

I can build a loop, but geezzz, these range objects are too nice to
have to fool around like that. Is there an enumerator somewhere.

Thanks
.

trim a range object
 

On Mar 23, 11:13*pm, JLatham
wrote:
There is an enumerator ... see the 4th post by FSt1 on how to implement it.