Is there a Faster Way (trim, clean VBA)

[Alkemist]

Hia...

I've written some code that selects all the text cells in a spreadsheet and cleans up the contents with Trim, Clean:

Range("A1", Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeConstants, 2).Select

For Each rng In Selection
rng.Value = Application.WorksheetFunction.Clean(Trim(rng.Value))
Next rng

Is there a faster way of doing this becaise some worksheets have a lot of data?

Cheers...A

 

[Weaver]

first of all, don't select a range to work with it.
Second, disable screen updating

You might find performance is much improved.

application.screenupdating=false
For Each rng In Range("A1", Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeConstants, 2)
rng.Value = Application.WorksheetFunction.Clean(Trim(rng.Value))
Next rng

 

[Alkemist]

Excellent..thanks for that, it was exactly what I was looking for.

I knew I didn't need to select the range, but, I couldn't work out how to code the VBA. I usually start by recording a macro and re-writting, recording always goes the route of selecting cells.

That was a small chunk of code from a larger script that does turn off screen updating, autocalc and so on, both of which are of course real time savers.

Thanks again

Cheers..A

 

[andrija]

procedure works well, but don't remove 'non breaking space' (NBSP) useing in Internet (Chr(160)), request of a little supplement as follows:
'--
Sub RemoveAllNonPrintableCharactersInSelection_v1()
Dim rng As Range
Dim c As Range
Dim lngMemoCalculation As Long
Const csBLANK As String = " "
'--
'For complete worksheet
'Set rng = Range("A1", Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeConstants, 2)
'... or for selected range only
Set rng = Selection.SpecialCells(xlCellTypeConstants, 2)
'--
Application.ScreenUpdating = False
lngMemoCalculation = Application.Calculation
Application.Calculation = xlCalculationManual
For Each c In rng.Cells
c.Value = Application.WorksheetFunction.Clean( _
Trim(Replace(c.Value, Chr(160), csBLANK)))
Next c
Application.Calculation = lngMemoCalculation ' Restore original Calculation mode
End Sub
'--
Best Regards
andrija vrcan