CODEWITHSUNDEEP
excelvba
FreeSoftwareServers
FreeSoftwareServers

Reputation: 2831

Adjusting Range Value without For Loop

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

Upvotes: 0

Views: 97

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

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

Using Variant method:

  1. load range into a variant array.
  2. loop the array and make changes.
  3. 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

Upvotes: 2

BigBen
BigBen

Reputation: 50162

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.

Upvotes: 3

Related Questions