Excel VBA - Speed up loop that edits all cells in a column-CodePudding

I'm trimming all entries in a column (except header in row 1) to the last four characters using:

Range("A2").Select

Do While ActiveCell <> ""
    ActiveCell = Right(ActiveCell.Value, 4)
    ActiveCell.Offset(1, 0).Select
Loop

It works, but is quite slow on large files. Does anyone know how I could speed this up?

CodePudding user response：

Along with the linked answers in the comments, I prefer to use variant arrays when looping. They are stored in memory. Whenever one accesses the work sheet vba needs to slow down. by limiting our interactions we can speed things up.

Sub right4()
    With ActiveSheet 'better to use actual worksheet ie Worksheets("Sheet1")
        Dim rng As Range
        Set rng = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
    End With
        
    Dim rngarr As Variant
    rngarr = rng.Value
    
    Dim i As Long
    For i = 1 To UBound(rngarr, 1)
        rngarr(i, 1) = Right(rngarr(i, 1), 4)
    Next i
    
    rng.Value = rngarr
End Sub

If one wants to not use a loop:

Sub right4()
    With ActiveSheet 'better to use actual worksheet ie Worksheets("Sheet1")
        Dim rng As Range
        Set rng = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
  
        rng.Value = .Evaluate("INDEX(RIGHT(" & rng.Address(0, 0) & ",4),)")
    End With
End Sub

Though I would guess that timing on the second will come a close second to the first code.