Improve speed on Trim function

Im trying to run a function that TRIM and CLEAN all cells in a range, using a worksheetfunction. However this takes forever since I loop trough all cells.
In order to test I have set range = A1:X100000, however in actual use this range may vary, and be up to 3-4 times bigger.
My current code now takes about 16-17 minutes, is there any better way to do what I want that improves the speed?

Function Clean_Trim()

Application.ScreenUpdating = False

    Dim CleanTrimRg As Range
    Dim oCell As Range
    Dim Func As WorksheetFunction
     
    Set Func = Application.WorksheetFunction
     
    On Error Resume Next
    Set CleanTrimRg = Worksheets("Import_data").Range("A1:X100000")
    If Err Then MsgBox "No data to clean and Trim!": Exit Sub
     
    For Each oCell In CleanTrimRg
        oCell = Func.Clean(Func.Trim(oCell))
    Next

Application.ScreenUpdating = True

End Function

Try this...

Function Clean_Trim()

Application.ScreenUpdating = False

    Dim CleanTrimRg As Range
    Dim v As Variant
    Dim oCell As Variant
    Dim Func As WorksheetFunction
     
    Set Func = Application.WorksheetFunction
    On Error Resume Next
    Set CleanTrimRg = Worksheets("Import_data").Range("A1:X100000")
    If Err Then MsgBox "No data to clean and Trim!": Exit Function
     
    v = CleanTrimRg
    For Each oCell In v
        oCell = Func.Clean(Func.Trim(oCell))
    Next
    CleanTrimRg = v

Application.ScreenUpdating = True

End Function

It reads the the cell range values into a variant array then clean-trims the array then writes the array back to the cells. It may seem counter-intuitive, but it is much faster than doing it one cell at a time.

Also, technically this is not a Function. It's a procedure. Functions return a value back to the caller.

@: Leith Ross and AlphaFrogs

Both your codes made quite a big difference in performance (AlphaFrogs: 3.02 minutes, Leith Ross: 47 seconds)
But none of your codes seems to actually TRIM or CLEAN the cells in the range, like my original code did.

try

Sub test()
    [a1:x100000] = [index(trim(clean(a1:x100000)),)]
End Sub

@jindon,
How do I get this to reference to the sheet I want to trim/clean?

Originally Posted by TommyN

@jindon,
How do I get this to reference to the sheet I want to trim/clean?

Something like

Sub test()
    With Sheets("sheet1")
        .Range("a1:x100000").Value = .Evaluate("index(trim(clean(a1:x100000)),)")
    End With
End Sub

@Leith Ross,

I made a new workbook for testing purposes, with some test data and narrowed the range.
As you can see, if you try to run the 3 different subs, only the first one will actually trim the extra spaces from the text, leaving just one space between each word and no spaces in front or at the end.

@jindon: thank you, this works and improves the speed at about 2 minutes compared to my original 17. Even though I don't understand what your code actually does.

However, Im wondering if this is the most efficient way, or are there still room for improvement? Because as I said my range may be up to 4 times bigger, and I would guess that this would take 4 times longer?

Leith Ross code took about half the time, but since it didnt' do anything I cant use it. Does someone knows whats wrong? Or know of other efficient ways?

Not sure if there is faster solution.

Regarding Leith Ross code
You can't change the elements in an array by For Each loop.

This will loop through each element in the array.

Sub Clean_AlphaFrog2()
    
    Dim CleanTrimRg As Range
    Dim v As Variant, i As Long, j As Long
    Dim Func As WorksheetFunction
    
    Set Func = Application.WorksheetFunction
    Set CleanTrimRg = Worksheets("Import_Data").Range("A1:D10")
    
    Application.ScreenUpdating = False
     
    v = CleanTrimRg
    For i = 1 To UBound(v, 1)
        For j = 1 To UBound(v, 2)
            v(i, j) = Func.Clean(Func.Trim(v(i, j)))
        Next j
    Next i
    CleanTrimRg = v
        
    Application.ScreenUpdating = True
    
End Sub

Also note; WorksheetFunction.Trim is not exactly the same as VBA function Trim.

WorksheetFunction.Trim will trim multiple consecutive spaces within the text.
VBA Trim will only trim leading and trailing spaces.

Example (periods represent space characters for illustration):
WorksheetFunction.Trim "..Test....Test.." becomes "Test.Test"
VBA Function Trim "..Test....Test.." becomes "Test....Test"

Hi-

For me, jindon's solution is the best but even further improved as below unless I am doing something wrong.

jindon, would calling once the evaluate boost the speed as below?
atleast in my PC I got the same data results and took 3.04688 seconds

Sub test2()
    Dim s As Single
    s = Timer
    With Sheets("Sheet1 (2)")
        .Range("a1:x100000").Value = .Evaluate("index(trim(clean(a1:x1)),)")
    End With
    Debug.Print "test2", Format$(Timer - s, "#.00000")
End Sub

Regards,
Event

.Evaluate("index(trim(clean(a1:x1)),)")

Thanks I went with jindons last solution where "test2" was fastest. It was far superior than anything else I tried. Still uses some time (17-18 seconds), but at least now users won't wait forever...