How to Trim a range of cell values when copied from recordset

[Zardax]

Hi There,
I'm adding a recordset to my sheet using the '.CopyFromRecordSet' function, however one column of the data has trailing white space.
Does anyone know of a non looping method to clear / trim this white space in bulk?

Cheers

Zardax

 

[Sandeep Warrier]

Hi and welcome to the Board!

Does the data consist of only one word? In that case you can use a simple Find/Replace to replace all spaces with blank.

If not, then I don't think this can be done without looping.

 

[shg]

Here's what I use. Select the column and run TrimSelection. Be careful not to apply it to formulas; it will replace with values. It will also reduce mutiple spaces interior in quoted strings ...

Sub TrimSelection()
    ' Ctrl+Shift+T
    TrimRange Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange)
End Sub
 
Sub TrimRange(r As Range)
    Dim rArea       As Range
    Dim sAdr        As String
 
    For Each rArea In r.Areas
        With rArea
            sAdr = .Address(ReferenceStyle:=Application.ReferenceStyle, _
                            External:=True)
            .Value = Evaluate("if(row(), substitute(" & sAdr & ", char(160), "" ""))")
            .Value = Evaluate("if(row(), trim( " & sAdr & "))")
        End With
    Next rArea
End Sub

 

[hi_vishy]

You can use trim in the sql query itself that is being used to create recordset.

 

[Zardax]

Hi Every-one,
Thanks for your quick repsonses. 'hi_vishy' you hit the nail on the head. Should've thought of this one earlier.
Sandeep - the data contains multiple columns and potentially thousands of rows hence wanting to avoid loops and the like.

Cheers

Zardax