Updated June 9, 2023
Excel VBA Replace Function
As in Excel, we have a function where we can find and replace any word or character, or sentence with any letter. But by that process, we can only replace one sentence or letter at a time. With the help of the VBA Replace Function, we can replace as many words, letters, or sentences in a single shot. This saves enormous time and effort doing single activity multiple times. We will use the Replace function in the built-in VBA function list for this.
Below are the syntax and argument of Replace function in VBA.
How to Use Excel VBA Replace Function?
We will learn how to use a VBA Replace Excel function with a few examples.
VBA Replace Function – Example #1
We have sample data of some sentences where we will replace one word and paste that updated sentence in Sentence B Column.
Follow the below steps to use the Replace function in Excel VBA.
Step 1: Go to VBA and select Module in the Insert menu tab.
Step 2: Write a sub-category in the name of a performed function or in any name of your choice, as shown below.
Code:
Sub VBA_Replace2() End Sub
Step 3: Now define a dimension as Long as we select a complete sentence. Here we have taken it as X.
Code:
Sub VBA_Replace2() Dim X As Long End Sub
Step 4: Now, in that Long X, select the maximum range till where our data could go up. Here we have taken as A1000 cell range, and for going up till our data starts, use End(xlUp) followed by a dot(.)Row. This means we will move up in the same column until that row with the data. This process in life is the Ctrl + Up arrow key in Excel.
Code:
Sub VBA_Replace2() Dim X As Long X = Range("A1000").End(xlUp).Row End Sub
Step 5: Now again, define one more dimension, Y, as Long as shown below.
Code:
Sub VBA_Replace2() Dim X As Long X = Range("A1000").End(xlUp).Row Dim Y As Long End Sub
Step 6: Now start a For Next loop for the second defined dimension Y from cell position 2 to till X range (last filled cell)
Code:
Sub VBA_Replace2() Dim X As Long X = Range("A1000").End(xlUp).Row Dim Y As Long For Y = 2 To X Next Y End Sub
Step 7: Now select the Value of Column B as Range as Y followed by a dot(.); this is like inserting a function in Excel.
Code:
Sub VBA_Replace2() Dim X As Long X = Range("A1000").End(xlUp).Row Dim Y As Long For Y = 2 To X Range("B" & Y).Value = Next Y End Sub
Step 8: As seen in the VBA syntax of Replace function above, type Replace and select Columns A and Y as the first expression of the string, followed by the words we need to replace from the table.
Code:
Sub VBA_Replace2() Dim X As Long X = Range("A1000").End(xlUp).Row Dim Y As Long For Y = 2 To X Range("B" & Y).Value = Replace(Range("A" & Y), "Delhi", "New Delhi") Next Y End Sub
Step 9: Now, compile the complete code and run. Once we do that, we will see that the word “Delhi” from Sentence A is replaced with the word “New Delhi,” and the whole sentence is copied to Sentence B.
We can select a string of words or a sentence and replace that with any required letters or sentences.
VBA Replace Function – Example #2
There is another way to replace words or sentences with the help of VBA coding. For this, we have a list of 7 subjects which are getting repeated. And we need to replace those subject names by adding serial numbers before each subject name, as shown below.
As we can see in the above screenshot, column E has the unique names of those subjects, which are there in column B, and column F has the subject terms with a serial number at the start of it.
Follow the below steps to use Replace function in VBA.
Step 1: Now, go to VBA, and from the Insert menu, add a new Module. Once we get it, start writing the Sub-category in the name of a function being performed, as shown below.
Code:
Sub VBA_Replace() End Sub
Step 2: Now consider a dimension Rng as Range, InputRng as Range, and ReplaceRng as Range. You can choose any other letters or words for defining ranges.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range End Sub
Step 3: Now, use xTitleId as a dialog box and give it a name. Here we have named it “VBA_Replace.”
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" End Sub
Step 4: Now assign the Application.Selection with InputRng will enable the selected application to be used in VBA.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection End Sub
Step 5: Now, in the next line, insert an InputBox of 3 types, Original range, xTitleId, and InputRng. The original range is a list of subjects that need to be replaced, listed in column B. xTitledId is the dialog box names in the performed function. And InputRng is a range of data tables in columns E and F.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) End Sub
Step 6: Now, in the next line, assign an Input for ReplaceRng, and for this, select Replace Range column. The rest of it is the same.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) End Sub
Step 7: Now, we will use the Application.screenupdating function; as the name says, it is used for updating if it is FALSE.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False End Sub
Step 8: Now insert a For-Next loop. And for each Rng range, replace the values from the ReplaceRng column.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells Next End Sub
Step 9: Finally, replace InputRng with the values present in Rng from the whole sheet.
Code:
Sub VBA_Replace() Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8) Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng In ReplaceRng.Columns(1).Cells InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole Next End Sub
Step 10: Once done, now compile and run the code. We will get and dialog box in the name of VBA_Replace. From here, select the list of subjects we need to replace as Original Range and click OK. Then we will get another box; from there, select the Replace range from E2 to F8. This table has the data that needs replacing, and click OK.
We will get Data in column B, which will get replaced from the data in column F with a serial number.
To an overview, column E has unique subject names. And column F has subject names with serial numbers. So by this, data in column B is replaced with data in column F.
Pros of VBA Replace Function
- We can replace multiple words or sentences in a single shot.
- There is no limit to words or text that we cannot replace with.
- Syntax of Replace in VBA is as easy as using the SumIf function in Excel.
- As shown in example-1, VBA Replace is the easiest way to apply.
Things to Remember
- Save as Marco Enabled Excel to avoid losing written code in VBA.
- Always consider the dimensions in such a way that it will create a value-added while we select those in code.
- Make sure you select the whole range of the sheet as shown in example-2 if it is limited to get the result. Or else you can define and select a limited cell range that would be used under Replace function.
Recommended Articles
This has been a guide to VBA Replace Function. Here we discussed VBA Replace, how to use Excel VBA Replace Function, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –