trim a range object

  • Thread starter Thread starter cate
  • Start date Start date
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
 
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
 
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...
 
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
 
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.
 
Back
Top