Some spreadsheets contain only a single sheet of data in a few columns and rows. Other spreadsheets contain tens or even hundreds of thousands of datapoints. The latter type often act as miniature databases for smaller businesses or teams within companies where building a full database is unnecessary. Often the datapoints are automatically populated, and they are not always checked for integrity before loading. Sometimes things just change and we need to globally update the information in a spreadsheet.

Whatever the reason, Find and Replace is an invaluable tool for anyone working with spreadsheets. The Excel GUI provides an easy-to-use dialogue to find and replace content throughout a spreadsheet, for both the data and the formulas. But what if, somewhere in the pipeline between origination of the data and its final residence in the spreadsheet, a flaw in the data is algorithmically introduced? No one wants to do exactly the same Find and Replace operation every time data is loaded or entered.

This is a programming blog, and around here we like to automate tasks, no matter how simple. It helps cut down on unnecessary, tedious work, but it also ensures our data is manipulated reliably. So, today let’s look at how to programmatically apply Find and Replace with VBA.

Our Dataset

Throughout this tutorial we’ll manipulate the data in this screenshot. You can download the CSV file here, if you’d like. It isn’t necessary to have all the data locally in your own workbook, but it serves as a good visual aid:

Screenshot of Dataset
A screenshot of the dataset

The Inefficient Approach

I want to start off with an inefficient approach, which is really just brute-force string manipulation. This is certainly not the most efficient, but by using looped string manipulations and if statements you could check every cell explicitly.

For example, let’s say you import customer information daily, and the currency label resides in Column F. For some reason, the supplied information used Australian dollars (AUD) instead of Canadian dollars (CAD) for your Canadian orders. Most orders are in US dollars (USD), but you do have some Canadian customers. You do not ship to Australia, so you don’t accept AUD.

In this case, to ensure data consistency, you want to replace every instead of AUD with CAD. This is a very straightforward task, and you can write and apply this three-liner in about five seconds:

Sub naiveApproach()

For i = 2 To 5000
    If Cells(i, 6).Value = "AUD" Then Cells(i, 6).Value = "CAD"
Next i
    
End Sub

However, as your dataset grows, this method can become time consuming. It also makes it harder to expand to multiple columns and rows. Fortunately, there is a simpler and more elegant solution.

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

The .Replace Method

One of the methods attached to the Range object is the .Replace method, which does exactly what you would expect. Be wary that this function is the GUI equivalent of “replace all” within the range, not just “replace the first instance”. In our currency example, we want to replace all instances anyway, so this is fine.

Instead of using a loop, you can set the entire range first and Excel will use its optimized internal code to most efficiently find and replace the errant AUDs. The code is a simple one-liner:

Range(Cells(2, 6), Cells(5000, 6)).Replace "AUD", "CAD"

And in the simplest terms, this searches the sixth column between rows 2 and 5000, replacing any instance of “AUD” with the letters “CAD”.

In letter-reference notation, you could use:

Range("F2:F5000").Replace "AUD", "CAD"

It’s important to recognize the Range.Replace method is different from the VBA Replace function, which is used to replace substrings within a string.

Expanding the Range

Of course, if you don’t know where in the spreadsheet certain information occurs, you can expand the range to anything you want. Perhaps your friend called you and said they accidentally entered “AUD” instead of “CAD” for every Canadian purchase. They need your help to fix the mistake! Moreover, they can’t show you the spreadsheet because it’s proprietary.

Well, you could tell them to write this line of code, which covers a gigantic area, captures every instance of AUD, and turns it into a CAD:

Range("A1:ZZ200000").Replace "AUD", "CAD"

Note: if your friend’s spreadsheet is bigger than this, they need to switch to a real database…

But alas! This has also changed “Audio” into “CADio”! What a disaster.

The Other .Replace Parameters

One pitfall of the .Replace method is that it is non-case-sensitive and replaces any matching string, regardless of whether that string fully matches. Thus, Audio is converted to CADio by mistake, because the three letters A-U-D are matched non-case-sensitively.

You can use the other parameters in the .Replace method to avoid issues like this, even when searching large areas. In this section, I’ll use the named parameter method (:=) of setting them, since there are so many parameters and multiple empty commas is unsightly and a bit confusing.

LookAt Optional Argument

The first optional argument is LookAt, which accepts two options: xlWhole or xlPart. The function defaults to the latter, so when we searched for AUD, it rewrote any string that contained those three letters in sequential order.

One way to avoid the debacle for our friend is to ensure this parameter is set to xlWhole:

Sub VBA_Replace()
Range("A1:ZZ200000").Replace What:="AUD", Replacement:="CAD", LookAt:=xlWhole
End Sub

Now the full word must match for a replacement. Since “Audio” is not exactly the same 3 letters as “AUD”, we don’t end up with “CADio”. If we had an option Audio Audi, xlPart will match both “aud” strings in both words, giving us CADio CADi. Luckily, you can easily avoid this now with LookAt:=xlWhole.

MatchCase Optional Argument

Another approach in this situation could be to match cases. Since we are searching for all uppercase currency codes, and we know the Item Type column is proper case, we could force the case to precisely match to get the same outcome:

Range("A1:ZZ200000").Replace What:="AUD", Replacement:="CAD", MatchCase:=True

A quick quiz for you: if we truly did not know the stylization of the data, would it be better to use LookAt or MatchCase?

Answer: LookAt, which forces the entire string to match. There is less chance that non-currency entries will be exactly the three letters A-U-D in that particular sequence, while it is very possible “AUD” might appear in multiple words, whether they’re capitalized or not. Had the Item Types used capital letters - as in AUDIO, PERIPHERAL, etc. - using only the MatchCase parameter would still have left us with CADIO.

Other Parameters

For finding and replacing strings in ranges, the other parameters are not important. Whether you choose to search by columns or by rows in SearchOrder is irrelevant for .Replace, because it will replace whatever it finds in all columns and all rows, regardless. Moreover, formatting is related to a completely different function and is not necessary for finding and replacing strings as we’ve explored here.

Replacing all instances of a string of text can be quite useful, particularly to fix mistakes or to update spreadsheets. However, it is important to think through how optional parameters might affect your output, because there is no simple undo button when using VBA. You certainly don’t want to make a mistake that affects 200,000 cells and cannot easily be undone.

If that worries you, I recommend saving your workbook before using .Replace. Or at least test it on a smaller range. You can remind your user to save, too, by running some code to programmatically initiate a Save As Dialog, if you want.

To learn more VBA tips like this one, subscribe using the form below.