How to Replace All in Excel VBA

1. Open the Excel workbook where you want to enter your VBA code. Press 'Alt' and 'F11' to open up the VBA console.
2. 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.
3. Copy the following formula into the module:Sub AReplace()Dim sb As WorksheetFor Each sb In Worksheetssb.Cells.Replace What:='XXX', Replacement:='YYY', LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseNextEnd SubChange '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.
4. 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:=FalseYou 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.
5. Click on the 'X' in the top-right corner of the VBA window to close it. All your changes are automatically saved.
6. 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.