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 below1, 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 Next End Sub
  1. please run this on a copy of the spreadsheet before running it, just in case the worst happens

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.