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

 

[Rick Rothstein]

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.

Here is my code modified to find the last row in each range no matter what column that last row is in...

Sub TrimColumnsB2DandJ2L()
  Dim Addr As String
  Addr = "B5:D" & Range("B:D").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Range(Addr) = Evaluate("IF(ROW(),TRIM(" & Addr & "),"""")")
  Addr = "J5:L" & Range("J:L").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Range(Addr) = Evaluate("IF(ROW(),TRIM(" & Addr & "),"""")")
End Sub

 

[hiker95]

monmon,

I'm wondering if I want to trim the whole of Columns B, C and D

Based on your latest request, the following should do what you have requested.

Option Explicit
Sub TrimRangeV2()
' hiker95, 02/05/2014, ME755373
Dim lr As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
With Range("B1:D" & lr)
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
End Sub

 

[monmon]

Here is my code modified to find the last row in each range no matter what column that last row is in...

Sub TrimColumnsB2DandJ2L()
  Dim Addr As String
  Addr = "B5:D" & Range("B:D").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Range(Addr) = Evaluate("IF(ROW(),TRIM(" & Addr & "),"""")")
  Addr = "J5:L" & Range("J:L").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  Range(Addr) = Evaluate("IF(ROW(),TRIM(" & Addr & "),"""")")
End Sub

Hi Rick,

This works very well! thanks for helping me!!

 

[monmon]

monmon,



Based on your latest request, the following should do what you have requested.

Option Explicit
Sub TrimRangeV2()
' hiker95, 02/05/2014, ME755373
Dim lr As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
With Range("B1:D" & lr)
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
End Sub

Hi Hiker,

Your code works well too. Thanks everyone for the great assistance!!

 

[hiker95]

monmon,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[zeke764]

Hi,

Can anyone explain why the IF is necessary in the Evaluate formula? How does that behave differently than:

Range(Addr) = Evaluate("TRIM(" & Addr & ")")

Thanks,

 

[Jonmo1]

The If(Istext is testing if the value in the range is TEXT.

You don't want to apply TRIM to a Numeric Value.
It will convert it to a "Number Stored As Text"

 

[zeke764]

Jonmo1,

Thanks for the quick response! That makes sense. What was confusing me is the ...IF(ROW()... version, I still don't follow that one.

 

[Jonmo1]

Disregard that...
The .Value converts the "number stored as text" to a real number..

But the Evaluate function needs something to make it behave like an array.
Otherwise, all cells end up containing the same value (the value in the top/left cell of the range)


The Row or IsText functions make Evaluate treat the formula as an array.
Trim by itself will not.

 

[zeke764]

Jonmo1,

Got it. Thank you for the response!