Trim to eliminate spaces for a few columns via VBA

[monmon]

Hi,

I have the following code that helps me to trim certain columns on a worksheet via VBA. The code is part of the macro that I am running. However, the VBA trim code causes the entire macro to run at a very slow pace.

Is it possible to modify the codes such that it will run faster?

Thanks in advance!

Dim RngZ As Range, C2 As Range, D2 As Range, RngM As Range, RngB As Range, D3 As Range

Set RngZ = Range("B5", Range("D" & Rows.Count).End(xlUp))

For Each C2 In RngZ
C2.Value = Application.Trim(C2.Value)

Next C2

Set RngM = Range("J5", Range("L" & Rows.Count).End(xlUp))

For Each D2 In RngM
D2.Value = Application.Trim(D2.Value)

Next D2

 

[AlphaFrog]

It may sound counter-intuitive, but it's faster to read all the values in a range to an array, trim the array, then write the array back to the range en masse.

Also, you could re-use the variables for each range. This wouldn't improve speed, but its a bit leaner.

[COLOR=darkblue]Dim[/COLOR] v  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
[COLOR=darkblue]With[/COLOR] Range("B5", Range("D" & Rows.Count).End(xlUp))
    v = .Value
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] v
        c = Application.Trim(c)
    [COLOR=darkblue]Next[/COLOR] c
    .Value = v
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    
[COLOR=darkblue]With[/COLOR] Range("J5", Range("L" & Rows.Count).End(xlUp))
    v = .Value
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] v
        c = Application.Trim(c)
    [COLOR=darkblue]Next[/COLOR] c
    .Value = v
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]

 

[Marcelo Branco]

See if this is faster

Sub atest()
    Dim rngZ As Range, RngM As Range, i As Long
    
    Set rngZ = Range("B5", Range("D" & Rows.Count).End(xlUp))
    
    For i = 1 To rngZ.Columns.Count
        rngZ.Columns(i) = Evaluate("=IF(ROW(1:" & rngZ.Rows.Count & _
            "),Trim(" & rngZ.Columns(i).Address & "))")
    Next i
    
    Set RngM = Range("J5", Range("L" & Rows.Count).End(xlUp))
    
    For i = 1 To RngM.Columns.Count
        RngM.Columns(i) = Evaluate("=IF(ROW(1:" & RngM.Rows.Count & _
            "),Trim(" & RngM.Columns(i).Address & "))")
    Next i
End Sub

M.

 

[Rick Rothstein]

See if this is faster

Sub atest()
    Dim rngZ As Range, RngM As Range, i As Long
    
    Set rngZ = Range("B5", Range("D" & Rows.Count).End(xlUp))
    
    For i = 1 To rngZ.Columns.Count
        rngZ.Columns(i) = Evaluate("=IF(ROW(1:" & rngZ.Rows.Count & _
            "),Trim(" & rngZ.Columns(i).Address & "))")
    Next i
    
    Set RngM = Range("J5", Range("L" & Rows.Count).End(xlUp))
    
    For i = 1 To RngM.Columns.Count
        RngM.Columns(i) = Evaluate("=IF(ROW(1:" & RngM.Rows.Count & _
            "),Trim(" & RngM.Columns(i).Address & "))")
    Next i
End Sub

This shorter piece of code should be a tad faster yet than the code you posted. Sorry AlphaFrog... both will be noticeably faster than your code (I think the repeated calls to Application.Trim is the reason)...

Sub TrimColumnsB2DandJ2L()
  Dim Addr As String
  Addr = "B5:D" & Cells(Rows.Count, "D").End(xlUp).Row
  Range(Addr) = Evaluate("IF(ROW(),TRIM(" & Addr & "),"""")")
  Addr = "J5:L" & Cells(Rows.Count, "L").End(xlUp).Row
  Range(Addr) = Evaluate("IF(ROW(),TRIM(" & Addr & "),"""")")
End Sub

 

[hiker95]

monmon,

Try the following.

Sub TrimRange()
' hiker95, 02/04/2014, ME755373
With Range("B5", Range("D" & Rows.Count).End(xlUp))
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
With Range("J5", Range("L" & Rows.Count).End(xlUp))
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
End Sub

 

[AlphaFrog]

AlphaFrog... both will be noticeably faster than your code (I think the repeated calls to Application.Trim is the reason)...

Changing to VBA.Trim was faster than the Evaluate column methods.

[COLOR=darkblue]Dim[/COLOR] v  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
[COLOR=darkblue]With[/COLOR] Range("B5", Range("D" & Rows.Count).End(xlUp))
    v = .Value
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] v
        c = VBA.Trim(c)
    [COLOR=darkblue]Next[/COLOR] c
    .Value = v
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    
[COLOR=darkblue]With[/COLOR] Range("J5", Range("L" & Rows.Count).End(xlUp))
    v = .Value
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] v
        c = VBA.Trim(c)
    [COLOR=darkblue]Next[/COLOR] c
    .Value = v
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]

65K rows
VBA.Trim: 2.08 seconds
Evaluate column 2.5 to 2.8 seconds

 

[Rick Rothstein]

Changing to VBA.Trim was faster than the Evaluate column methods.

BUT... the VBA Trim function does not do the same thing as the WorksheetFunction (or Application) Trim function, so the comparison is not a fair one. The VBA Trim function only removes leading and trailing spaces whereas the WorksheetFunction Trim function does that and collapses multiple interior space down to single spaces as well. Hence, using the VBA Trim function could leave the OP with a different result than what he was actually after.

 

[monmon]

Hi,

Thanks for all your help!!

learned something and VBA.trim and Application.trim.

I was just wondering what if I've spaces in between??

I think the codes are not going to work?

	Column B	Column C	Column D	Column E
5	TTT		HHH
6
7			HHH
8
9	GGG

<tbody>
</tbody>

 

[Rick Rothstein]

I was just wondering what if I've spaces in between??

I think the codes are not going to work?

As far as I can see, all of the code that has been posted in this thread should work fine whether there are blank cell in the ranges or not. Are you seeing something different?

 

[monmon]

As far as I can see, all of the code that has been posted in this thread should work fine whether there are blank cell in the ranges or not. Are you seeing something different?

There is nothing wrong with your codes and I appreciate that you've taken valuable time in explaining and teaching me.

I realised that my codes and all of your contributed codings do take care of blank cells within a certain range.

However, the .End(XlUp).Row only allows a certain range to be scanned for trim.

For example, the range B5 to D10. If I've a data in C100, then C100 will not be scanned for trim.

I'm wondering if I want to trim the whole of Columns B, C and D, how should I change the codes and at the same time allow it to run faster.

Addr = "B5:D" & Cells(Rows.Count, "D").End(xlUp).Row