TRIM in VBA on a Range

[ilcaa]

i have a range with 6000 rows, i want to apply a worksheet.TRIM function within VBA to eliminate spaces in-between and on the ends...

problem is... nothing happens. whats best syntax for this? thanks

<code>

set rng = range("a1:a6000")

With rng

.value = WorksheetFunction.Trim(.value)

End With


</code>

 

[Rick Rothstein]

Just for fun, this also can be used:

Sub TrimRange()
  With Range("A1:A6000")
    .Value = Application.Trim(.Value)
  End With
End Sub

And, since the range is fixed in size, either of these one-liners will also work...

Sub TrimRange()
  [A1:A6000] = [IF({1},Trim(A1:A6000))]
End Sub

Sub TrimRange()
  [A1:A6000] = Application.Trim([A1:A6000])
End Sub

 

[Fluff]

Or, for anyone using Office 365

Sub TrimRange()
  [A1:A6000] = [Trim(A1:A6000)]
End Sub

 

[Rick Rothstein]

Or, for anyone using Office 365

Sub TrimRange()
  [A1:A6000] = [Trim(A1:A6000)]
End Sub

Office 365 does not require the array processing to be induced into text functions? When I run your code line in XL2010, it clears all of the cells which is why I used the IF function call that I did in my first macro (Message #11 ) in order to induce the array processing.

 

[Fluff]

Whilst I still use the If function, as most people are not using 365, it seems that it's no longer needed. I've tried it with various different formulae & it works without the If.
I'm guessing that It's got something to do with the new array functions, even though I don't have them yet.

 

[Rick Rothstein]

Whilst I still use the If function, as most people are not using 365, it seems that it's no longer needed. I've tried it with various different formulae & it works without the If.
I'm guessing that It's got something to do with the new array functions, even though I don't have them yet.

Interesting... well atleast Microsoft is maintaining backward compatibility.