Is there any way to change range's values more efficiently than looping?

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
I have a range of cells with values inside. The values have pattern such as '123-abc'. Now I need to change the value of selection to just 123.
I currently use left function to loop over the selection. But it's very slow because I have 1000 cells.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Is that in a macro ? if so, this for example, should do what you want without the need for looping.

Code:
Range("A1:A1000") = Evaluate("=LEFT(A1:A1000,3)")

Change range address as required.
 
Upvote 0
That's awesome Tribak, but is there anyway to use Evaluate function for range variable and Selection?
eg: Selection: = Evaluate(..Selection)
Dim rng As Range
rng = Evaluate(...rng)
 
Upvote 0
You mean like this :
Code:
Range("A1:A1000") = Evaluate("=LEFT(" & Range("A1:A1000").Address & ",3)")

Or amybe wrap the code in a seperate sub for more flexibility :
Code:
Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate("=LEFT(" & R.Address & "," & Length & " )")
End Sub


Then you just pass to the above Sub the range of your choice and the number of characters you wish to extract :
Code:
Sub [B]Test1[/B]()
    Call LeftWithoutLoop(Range("A1:A1000"), 3)
End Sub

Or :
Code:
Sub [B]Test2[/B]()
    Call LeftWithoutLoop(Selection, 3)
End Sub
 
Last edited:
Upvote 0
Hi Tribak, that is a complete solution. Thank you!
Do you know any guide or tutorial for Evaluate function, I already search online but didn't find a good one.
 
Upvote 0
Hi Tribak, that is a complete solution. Thank you!

Do you know any guide or tutorial for Evaluate function, I already search online but didn't find a good one.

You are welcome.

I don't know of a tutorial but a quick search came up with this discussion :
https://www.ozgrid.com/forum/forum/...evaluate-most-powerful-command-in-vba?t=52372

I am sure, there should be more info about this useful function scattered over the net. I would just keep looking.
.
Good luck.
 
Last edited:
Upvote 0
Hi Tribak,

After checking your solution would fail for multiple cells with different values. Do I miss anything?
Eg: A1="123-abc", A2="456-xyz"they wouldd be both 123
I just need A1="123" and A2="456"
 
Upvote 0
String functions do not natively process arrays, so it must be "induced". If there are no blanks within your range, these (from Jaafar's code in Message #4 ...
Code:
Range("A1:A10") = Evaluate("[B][COLOR="#FF0000"]IF({1},[/COLOR][/B]LEFT(" & Range("A1:A10").Address & ",3)[B][COLOR="#FF0000"])[/COLOR][/B]")

Code:
Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate("[B][COLOR="#FF0000"]IF({1},[/COLOR][/B]LEFT(" & R.Address & "," & Length & ")[B][COLOR="#FF0000"])[/COLOR][/B]")
End Sub

If, on the other hand, there could be blanks within your data, we need to test for them or else they will become zeros...
Code:
Range("A1:A10") = Evaluate("[B][COLOR="#FF0000"]IF(" & Range("A1:A10").Address & "="""","""",[/COLOR][/B]LEFT(" & Range("A1:A10").Address & ",3)[B][COLOR="#FF0000"])[/COLOR][/B]")

Code:
Sub LeftWithoutLoop(ByVal R As Range, ByVal Length As Long)
    R = Evaluate([B][COLOR="#FF0000"]Replace([/COLOR][/B]"[B][COLOR="#FF0000"]IF(@="""","""",[/COLOR][/B]LEFT([B][COLOR="#FF0000"]@[/COLOR][/B]," & Length & "))"[B][COLOR="#FF0000"], "@", R.Address)[/COLOR][/B])
End Sub

Note: The above is untested, but I think I got it correct.
 
Last edited:
Upvote 0
Hi Mr Rick,

All of them are tested successfully, your answers are more than I need. Can you explain why you put the If clause to induce array?
For the two final ones, can you provide cases to illustrate as I don't see any differences.
 
Upvote 0
Can you explain why you put the If clause to induce array?
I cannot give you a technical reason... it is just something I discovered when playing around with the Evaluate function when I first learned about it a few years back. The non-technical reason that I concluded after much testing is that text functions do not return arrays natively; however, if you embed them within a function that can process array (the IF function for example), then that seems to induce array processing in the text functions.



For the two final ones, can you provide cases to illustrate as I don't see any differences.
None is needed... I did nothing to change how they interact with the LeftWithoutLoop function, so just use them as Jaafar instructed.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top