top of page

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


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page