Does anyone know the optimised VBA code for Search and Replace Next/All that is built in Excel?

[excelos]

Hello!

Does anyone know the optimised VBA code for Search and Replace Next/All that is built in Excel?

I would assume it is not just a loop or something and I would like to use this as a template for any looping!

Thanks!

 

[Logit]

Record a macro using those menu selections. Then review the macro that was created.

 

[Alex Blakenburg]

How about:

Sub ReplaceText_NoLoop()

    Dim rng As Range
    Dim arr As Variant
    
    Set rng = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
    arr = rng.Value2
    
    ' Replace Apple with Big Apple
    arr = Application.Substitute(arr, "Apple", "Big Apple")
    
    rng.Value2 = arr

End Sub

20230310 VBA Array Replace excelos.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item	Description		<--- Before			Item	Description		<--- After
2	Apple	The Apple is sweet					Big Apple	The Big Apple is sweet
3	Orange	The Orange is sweet					Orange	The Orange is sweet
4	Pear	The Pear is sweet					Pear	The Pear is sweet
5	Kiwi	The Kiwi is sweet					Kiwi	The Kiwi is sweet
6	Apple	The Apple is sweet					Big Apple	The Big Apple is sweet
7	Orange	The Orange is sweet					Orange	The Orange is sweet
8	Pear	The Pear is sweet					Pear	The Pear is sweet
9	Kiwi	The Kiwi is sweet					Kiwi	The Kiwi is sweet
Data