Using trim function in VBA, taking time, can we reduce the time

[ThrottleWorks]

I am using below mentioned code to trim the data.

Dim Ducati As Range
Set Ducati = Range(Cells(1, 1), Cells(LRw, LCol))
 
For Each cell In Ducati ' Ducati is defined as range
    If cell.Value <> "" Then
          cell.Select
          ActiveCell = Trim(ActiveCell)
    End If
Next

This code takes time to complete the opetation.
I wanted to know, is there more efficient way to do this.

Background about the code - This (code) is part of another macro.
The macro opens the file, copies the data based on the criteria and paste in the report.

Since this code trims the data from approx 200 files it takes time to run.
And without trimming data I can not go further.

What I am doing in the code is, if cell is non blank then trim else leave.
Can anyone please help me in this.

Not uploading sample file due to some reason but please let me know if required.

 

[SirJB7]

Hi, ThrottleWorks!

The main problem is the select operation. Select method is highly resource consuming and generally you can avoid it. In this case change the inner part of the If...EndIf structure to:

          cell = Trim(cell)

and give it a try.

Regards!

 

[ThrottleWorks]

@SirJB7 , You are correct (as always) ! before posting I was trying to do it without selecting the cell but could not do it.

Thanks a lot for the help, have a nice day ahead.

 

[Debraj]

Hi Sachin,

Just to keep the fame of DUCATI..
You can save few more time.. by reducing Loop also..

Sub TrimLargeRange()
Dim Ducati As Range
Set Ducati = Range("A1:PP999")
Ducati = Evaluate("=IF(ROW( " & Ducati.Address & " )*( " & Ducati.Address & " <>""""),TRIM( " & Ducati.Address & " ),"""")")
End Sub

PS: who cares in case of TRIM .. cell is blank or not..
PPS: just feel the speed.. take a ride..

 

[SirJB7]

Hi, ThrottleWorks!

I was trying to find how to avoid the loop thru the cells too, but I didn't succeed with the actual and modern VBA features. I always forget the ancient and arcane Evaluate, it's from stone era when @Debraj was at school. But take a ride.

Regards!

 

[ThrottleWorks]

@Debraj , sorry for late reply. I read your post yesterday only but could not reply from mobile.

I was selecting the cell then trimming it, to reduce the time I used if non-blank then trim.
Thought it would save time. I am trying your code, will share the results ASAP.

Not checked the code yet but have an initial doubt why "Range("A1P999")". I think I might get answer while running the code.

Thanks a lot for the help.

@SirJB7 , good morning.

 

[shrivallabha]

I'd think this one would work and be faster. Lightly tested.

Public Sub Trim()
Dim Ducati As Range
Set Ducati = Range("A1:C11")
Ducati.Value = Application.Trim(Ducati.Value)
End Sub

 

[ThrottleWorks]

@Debraj & @shrivallabha thanks a lot for the help. Sorry for the late reply.

Tried the code for range ("a1p999") for the value "A ". Took 5 and 3 seconds to complete.
Could not try on 200 files as of now.

Debraj, you sure deserve a Ducati. Shrivallabha nice to see you after long time.

Have a nice day ahead.