Trim to eliminate spaces for a few columns via VBA

monmon

Board Regular
Joined
Apr 1, 2013
Messages
84
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!

Code:
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
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.

Code:
[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]
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
See if this is faster

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
See if this is faster

Code:
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)...

Code:
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
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

monmon,

Try the following.

Code:
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
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,379
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.

Code:
[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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 1, 2013
Messages
84
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 BColumn CColumn DColumn E
5 TTT HHH
6
7 HHH
8
9 GGG

<tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Apr 1, 2013
Messages
84
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.


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

Watch MrExcel Video

Forum statistics

Threads
1,122,599
Messages
5,597,105
Members
414,125
Latest member
iQQ

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
Top