trim a range object

C

cate

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
 
F

FSt1

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
 
F

FSt1

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

regards
FSt1
 
F

FSt1

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 said:
Sub trimrng()
Dim r As Range
Set r = [A1]
r.Value = Trim(r.Value)
End Sub

regards
FSt1

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

regards
FSt1
Click to expand...
Click to expand...
 
J

JLatham

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
 
C

cate

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
Click to expand...
Thanks. I tried you suggestion with my range object. It worked fine.
I had a whole row
so I didn't need the End(xup)... what is that? :).

Dim TrimIt As Range
Set TrimIt = myTC.Range(myTC.Cells(Make_FirstDataRow.Row,
TC_DATE.Column), _
myTC.Cells(Make_LastDataRow.Row,
TC_DATE.Column))

Dim c As Range
For Each c In TrimIt

If c.Value2 <> "" Then
Dim s
s = "value is " & c.Value
MsgBox (s)
End If

Next c

Thanks all for the help.
 
Top