script working perfect in 2013 but not in 2010

J1enter

New Member
Joined
Nov 17, 2010
Messages
16
Dit VBA script works perfect on my Excel 2013.

Code:
Dim cell As Range, areaToTrim As Range
Set areaToTrim = Sheet1.Range("A:A")
For Each cell In areaToTrim
    cell.Value = RTrim(cell.Value)
Next cell

But when my colleague, using Excel 2010, runs it, it loops endlessly. Can I make an adjustment in this code to make it run in 2010?

More background on the purpose of this command: In column A there are clientnames with sometimes a trailing space. These need to be deleted. But there are also clientnames with double spaces in between words. These need to stay. Hence the use of RTRIM. Oh..one thing I noticed: the actual name of sheet1 is 'worklist', but the script works on 2013 nonetheless.
 
Last edited:

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,206
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Have you tried limiting the range to trim like this?

Set areaToTrim = Intersect(Sheet1.Range("A:A"), Sheet1.UsedRange)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
I don't see anything in that code that wouldn't work in any version of Excel.

Are you sure it's looping endlessly in 2010?

The loop you have is finite as there is a finite number of rows, so the loop will end, eventually.
 

J1enter

New Member
Joined
Nov 17, 2010
Messages
16
We waited for over 10 minutes for the macro to finish. On my computer it took less than a minute. After this 10 minutes we aborted the task. So, you're right, I'm not sure if it would have finished eventually.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

As I said the loop is finite, so the code would finish eventually.

Does the workbook that it was taking so long in have any other code?

For example a worksheet change event that could be triggered by this code?
 

J1enter

New Member
Joined
Nov 17, 2010
Messages
16
Yes it does. The actual script has 3 commands. We tested all 3 commands seperately and only the 'trim trailing spaces' is causing the delay. This is the entire code:
Code:
Sub replace_characters()
'
' replace_characters Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Columns("A:A").Select
    Selection.Replace What:=".", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Selection.Replace What:="&", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'above are 2 of total 18 replacement commands

'trim trailing spaces
Dim cell As Range, areaToTrim As Range
Set areaToTrim = Sheet1.Range("A:A")
For Each cell In areaToTrim
    cell.Value = RTrim(cell.Value)
Next cell

'maximize clientname to 35 characters
For Row = 3 To 2000
Sheets("Worklist").Cells(Row, 1) = Left(Sheets("Worklist").Cells(Row, 1), 35)
Next Row

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Have you tried, as Joe suggested, narrowing down the range the code runs on?
Code:
Sub replace_characters()
Dim rng As Range
Dim cell As Range

    '
    ' replace_characters Macro
    '
    ' Keyboard Shortcut: Ctrl+q
    '

    Set rng = Sheets("Worklist").Range("A1", Sheets("Worklist").Range("A" & Rows.Count).End(xlUp))

    With rng
        .Replace What:=".", Replacement:=" ", LookAt:=xlPart, _
                 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                 ReplaceFormat:=False

        .Replace What:="&", Replacement:=" ", LookAt:=xlPart, _
                 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                 ReplaceFormat:=False

        ' add in rest of replacement code
    End With


    For Each cell In rng
        cell.Value = RTrim(cell.Value)
        cell.Value = Left(cell.Value, 35)
    Next cell
    
End Sub
 

J1enter

New Member
Joined
Nov 17, 2010
Messages
16
Wow, your version insanely speeded up the runtime to less than a second! Thank you and JoeMo for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top