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

Zardax

New Member
Joined
Dec 29, 2010
Messages
13
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:confused:
 
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.
 
Upvote 0
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 ...
Code:
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
 
Upvote 0
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top