script working perfect in 2013 but not in 2010

[J1enter]

Dit VBA script works perfect on my Excel 2013.

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.

 

[JoeMo]

Have you tried limiting the range to trim like this?

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

 

[Norie]

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]

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]

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]

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:

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]

Have you tried, as Joe suggested, narrowing down the range the code runs on?

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]

Wow, your version insanely speeded up the runtime to less than a second! Thank you and JoeMo for your help!