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

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
.

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
.

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
.

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
.

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
.

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