How to Replace All in Excel VBA

Save

When you need to automate tasks in Microsoft Excel 2010, you can turn to Visual Basic for Applications, a programming language that is included with all modern versions of Microsoft Office programs for Windows (VBA support was removed for Mac Office in version 2008 but replaced in version 2011). VBA allows you to run a small program over a number of Excel cells, unlike Excel formulas, which only work on the cell they are in. While the Excel program comes with a find and replace wizard, you can use VBA to find and replace cell contents without requiring any user input, and you can do so across all worksheets in the workbook.

  • Open the Excel workbook where you want to enter your VBA code. Press "Alt" and "F11" to open up the VBA console.

  • Double-click on the module where you want to enter your code. If there is no module you can right-click on a worksheet, move your mouse over "Insert" and choose "Module." Then double-click on the module to bring it up.

  • Copy the following formula into the module:

    Sub AReplace()

    Dim sb As Worksheet

    For Each sb In Worksheets

    sb.Cells.Replace What:="XXX", Replacement:="YYY", LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False

    Next

    End Sub

    Change "XXX" to the value you are searching for and "YYY" to the value you want to replace it with. You can also change the "MatchCase" value to "True" if capitalization is important. This macro will find and replace all desired values on every worksheet in your workbook.

  • Use the following code if you only want to replace all the values in a given selection:

    Selection.Replace What:="XXX", Replacement:="YYY", LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False

    You can use this code within any other VBA macro as long as you enter this code after you have defined a selection area in your macro.

  • Click on the "X" in the top-right corner of the VBA window to close it. All your changes are automatically saved.

  • Click the "Developer" tab and press the "Macro" button. Choose your macro from the list and click "Run" to replace all the targeted values in your worksheet or workbook.

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!