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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
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.
 

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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)
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
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:

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29

ADVERTISEMENT

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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
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:

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29

ADVERTISEMENT

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"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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:

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,886
Messages
5,598,660
Members
414,252
Latest member
Tejeci

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
Top