Visual Basic Code to Find and Replace Strings on Multiple Worksheets

Tonight's tip is on visual basic code which is a variation on the code post as the Tip of the Night for November 27, 2017, which showed how to find and replace multiple terms in multiple workbooks. The vba code posted below lets you find and replace a string in all worksheets in a workbook in a specified range and also perform other formatting changes. Thanks to SWa for posting this code here.

As always, simply press ALT + F11 to enter Visual Basic. Right click on your workbook in the VBA Project list on the left, and insert a new module. Paste the code into the module. The line beginning, ".Rows("1:1").Replace What" can be edited to address '.Columns' rather than '.Rows', which can be identified with letters in the parentheses, ("B:B"). You can also specify a range as, ".Range("B1:B7")".

So we can start with a spreadsheet containing two worksheets like these:

. . . can be edited with this VBA code:

. . . to make these changes:

Sub sample_code() Dim ws As Worksheet

For Each ws In Worksheets With ws .Rows("1:1").Replace What:="HR", Replacement:="Home run", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False .Range("A:A,D:J").Delete Shift:=xlToLeft .Columns("F:P").Delete Shift:=xlToLeft .Columns("A:E").EntireColumn.AutoFit .Columns("B:B").ColumnWidth = 30.86 .Range("A1:E1").Font.Bold = True End With Next ws End Sub