Excel: Find and replace across all worksheets

I needed to remove carriage returns, line feeds and tab characters from an Excel spreadsheet that was copied straight out of Microsoft Access, and have written the script below 1please run this on a copy of the spreadsheet before running it, just in case the worst happens, which has the other options for replacing if you need to change it:

Option Explicit
Sub FindAndReplaceInAllWorkSheets()
    Dim WS              As Worksheet
    Dim Search          As String
    Dim Replacement     As String
    Search = Chr(10) 'Find Lf
    'Other options
    'Search = Chr(9) 'Find tabs
    'Search = Chr(13) + Chr(10) 'Find CrLf
    'Search = Chr(13) 'Find Cr
    Replacement = "
    For Each WS In Worksheets
        WS.Cells.Replace What:=Search, Replacement:=Replacement, _
        LookAt:=xlPart, MatchCase:=False
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.