Adjusting Range Value without For Loop-CodePudding

I want to remove the left character from a column of strings without looping over each cell, but I'm getting an error in Excel. Here is my code, can I do this without a for loop?

Public Sub TestRngAdjust()
 Dim TestRNG As Range
 Set TestRNG = Range("A1:A5")
 TestRNG.NumberFormat = "@"
 TestRNG.Value = Right(TestRNG.Value, Len(TestRNG.Value) - 1)
End Sub

CodePudding user response：

If you don't want to loop:

Dim s As String
s = "RIGHT(" & TestRNG.Address & ",LEN(" & TestRNG.Address & ") - 1)"
TestRNG.Value = TestRNG.Parent.Evaluate(s)

But really, it's very easy to read the data into a Variant array, use Right on each element, then write the array back to the range.

CodePudding user response：

Loops are not bad. They are bad when looping ranges on worksheets. Use variant arrays to loop.

Using Variant method:

load range into a variant array.
loop the array and make changes.
assign variant array data back to range.

Public Sub TestRngAdjust()
 
 Dim TestRNG As Range
 Set TestRNG = Range("A1:A5")
 
 Dim rngarr As Variant
 rngarr = TestRNG.Value
 
 Dim i As Long
 For i = 1 To UBound(rngarr, 1)
    rngarr(i, 1) = Mid$(rngarr(i, 1), 2)
 Next i
 TestRNG.NumberFormat = "@"
 TestRNG.Value = rngarr
 
End Sub