VBA Find and Replace

Excel VBA Find and Replace

Have you ever thought of automating Find and Replace function in Excel. In VBA, we can create a macro which can be used for finding and replacing anything which we normally do in Excel by pressing Ctrl + H shortcut keys. With the help of VBA Find and Replace, we can automate the finding of any word and replacing that with other replacement. This helps when we need to perform the same activity multiple times.

How to Find and Replace Words in Excel VBA?

Below are the different examples to find and replace the words in excel using VBA code.

VBA Find and Replace – Example #1

In a very simple example, we have a data set which consists of some name. And some of the names are getting repeated as shown below.

Now we will create a code, by which we will find any of the names which is getting repeated and replace with something else. For this, follow the below steps.

Step 1: Go to Insert menu in VBA and select Module option as shown below.

Step 2: Once we do that, we will get a new module opened. In that module, write the subcategory in the name of VBA Find and Replace or you can choose any name of that subcategory.

Code:

Sub Find_Replace1()

End Sub

Step 3: As we have the data in column B from cell B2 to B10, so we will select that Range first from B2:B10.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace

End Sub

Step 4: Now to replace something we need to look for that word. Here also we will first select the Replace function to look at what we need to replace.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace

End Sub

As per the syntax of the Replace function, we will find what we need to replace.

Step 5: Let’s select the first name of the list “Ben” as what we need to replace.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace What:="Ben"

End Sub

Step 6: Now choose the replacement word by which we need to replace the selected word. Here we chose to replace name Ben with Sam which is mentioned as Replacement as shown below.

Code:

Sub Find_Replace1()

Range("B2:B10").Replace What:="Ben", Replacement:="Sam"

End Sub

Step 7: Now compile the code and run it by clicking on the Play button or by pressing the F5 key, as shown below.

We will see, all the cells containing the name as Ben is now replaced with name Sam. And that is highlighted in yellow color as well.

VBA Find and Replace – Example #2

We all might have faced a situation where we tried to find and replace some words with case sensitive letters. Suppose in a list, if we want to replace only that word which has some lower case or upper case letters. And if we are using the process which we have seen in example-1 then we would end up replacing all the similar words in that list along with required one. This process cannot be done with the help of find and replace function (Ctrl + H) of excel. For this, we have a provision in VBA.

Let’s consider the below data for this. As we can see, we have purposely added a word BEN in upper case in cell B2.

Follow the below steps to find and replace the word BEN.

Step 1: Start the subcategory of VBA Find and Replace there as shown below.

Code:

Sub Find_Replace2()

End Sub

Step 2: Select the list as Range from B2 to B10.

Code:

Sub Find_Replace2()

Range("B2:B10")

End Sub

Step 3: Now choose the exact word which we want to replace. Here we have selected BEN which is in cell B2.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN"

End Sub

Step 4: Again, we have selected the same word which we have seen in example-1 as replacement of BEN as Sam.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN", Replacement:="Sam"

End Sub

Step 5: Now for replacing the exact word BEN, we need to use MatchCase if that becomes TRUE as shown below.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN", Replacement:="Sam", MatchCase:=True

End Sub

The MatchCase will help us in identifying that word which we want to replace with an exact match. Here, the word which we have selected is BEN in upper case and list has other similar words as well but in proper case.

Step 6: Now run the code by clicking on the Play button or by pressing the F5 key.

We will see, the word BEN which was at cell B2 is now replaced with the word “Sam”, highlighted in yellow color. And the similar words Ben which are located at cells B5 and B8 respectively are still unchanged.

This is how exact match replaces works.

Step 7: Now, we will remove the added MatchCase from the above code and see how this would work.

Code:

Sub Find_Replace2()

Range("B2:B10").Replace What:="BEN", Replacement:="Sam", MatchCase:=True

End Sub

Step 8: Again compile and run the code.

We will see the code has replaced all the cells containing word Ben as shown above. Which means, after removing MatchCase, the code will work as we have seen example 1.

Pros of Excel VBA Find and Replace

• Major learning is, we can replace case sensitive words and cell content with the help VBA Find and Replace as shown in example-2.
• If the activity is manually and multiple times getting repeated then automating this would be an advantage in saving time and effort both.
• Even if we have a huge set of data where we want to replace specific words, that can be done without crashing the excel in bulky files.

Things to Remember

• We can create a macro of Find and Replace function with the help of Record Macro option under the Developer tab. This is the easiest way to create a macro if you are new to coding.
• Saving the file in Macro Enable excel format will enable to code getting used in future.
• We can replace any kind of text, word, number or character by VBA Find and Replace.

Recommended Articles

This is a guide to VBA Find and Replace. Here we discuss how to find and replace words in Excel using VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Break for Loop
2. VBA Get Cell Value
3. VBA Find
4. FIND Function in Excel