Trim the entire range

[Guest]

Hi,

I have the following code that loops through a certain range.
For Each cel In ADINames
cel.Value = Trim(cel.Value)
Next cel

Can this be done on the entire range at once, i.e, w/o looping?

Thank you,

 

[Daniel Gash-Jones]

you can use an array formula ie:

dim rng as range, trimRng as range

set rng = sheets("Spare Sheet").resize(trimRng.rows.count, trimRng.columns.count)

application.screenupdating = false
rng.formulaarray = "=trim(trimRng)"
rng.parent.activate
rng.select
selection.copy
trimRng.parent.activate
trimRng.select
activesheet.pastespecial Paste:=xlPasteValues
with application
.cutcopymode = false
.screenupdating = false
end with
HTH

 

[Dave Peterson]

If the range is a single column, you could try:

Selecting the range
data|text to columns
Fixed width and then click finish.

If you need a macro, record it when you do it manually.