Trimming cells

[UnsolvedCoding]

Is it possible to trim all the cells on a worksheet, in a workbook or in a range?

Something like Trim(cells) or Trim(Range("A3:C55")?

 

[SkipVought]

hi,

TRIM is a String Function.

Cells or Range of cells is an OBJECT collection, and one of the properties of each Range in the collection, of which there ate many that have nothing to do with string, is Value, which a string.

The short answer is no. Each range.value in the collection must be trimmed...

dim r as range
for each r in YourRangeObject
  r.value = trim(r.value)
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

I was thinking that might be the case...

Oh well, thanks anyway.

 

[SkipVought]

if this is a one time thing, it's just as easy to do this directly in the sheet in a "helper column" using the TRIM() function. then COPY that column and Edit > PasteSpecial -- VALUES over the column to change. and finally delete the helper column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

I will play with it. The information on the worksheet comes from various forms ranging from SQL to user input.

Currently its coded so that the variables are collected as variable = trim(Range).value to eliminate any extra spaces, but doing a paste special may save considerable time.

 

[SkipVought]

Currently its coded so that the variables are collected as variable = trim(Range).value

if Range is a SINGLE RANGE reference, then that's all you need.
if Range is a MULTIPLE RANGE reference, then you have a problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}