0

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?

mjkutzman
  • 27
  • 6
  • you got a link to an answer that would give you the path in your last question that was deleted: https://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells Did you try to modify that to work for you? – Scott Craner Nov 08 '21 at 17:31
  • 1
    Also, usind `.Select` is slow. See https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Nov 08 '21 at 17:33
  • Thanks, Scott. I wasn't able to get the `Index` method to work. I modified and played around with it but couldn't get it to return anything other than the classic #VALUE! error. – mjkutzman Nov 08 '21 at 17:38
  • could you show the code that you tried that used the evaluate method? – Scott Craner Nov 08 '21 at 17:45
  • Scott, I'm good with the improved looping method you showed me. No need take up your time with troubleshooting my code for evaluate method. Thanks again. – mjkutzman Nov 08 '21 at 18:03

1 Answers1

1

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81