VBA・マクロ

【簡単に使える】指定した行/列/範囲で複数の指定した文字列を一括置換するマクロツール

 

日常業務を行うにあたって、エクセル内の一部分に行/列/範囲内に絞って大量の文字列を置換したいと思ったことはないでしょうか。

 

エクセルの表に検索する文字列と置換する文字列をいくつも指定して実行すると、指定した全ての文字列を全て一括で置換してくれるツールをマクロで作成しましたので、是非ご活用頂けたらと思います。

 

なおこちらのツールは、以下のツールを使用いただいた方からコメントにてExcelファイル内の限定した範囲において複数の文字列などをクリック一回で置換するツールの要望がありました

【知らなくても使える】複数文字列を一括で置換するマクロツール(Excel VBA) 日常業務を行うにあたって、エクセル内のある大量の文字列を置換する必要があるけど、どうしよう。と困ったことはないでしょうか。 ...

 

『指定した範囲での複数文字列の一括置換ツール』の概要

エクセルの表内に検索する文字列置換する文字列を記載した状態で、当該ファイル内、または対象のファイルをダイアログで指定すると、そのファイル内の指定したシートを対象に検索する文字列を置換する文字列に変換してくれます。

 

ExcelにてVBAコードを記載したマクロを作成し、ボタン(図形)をクリックすることでマクロを実行させて一括置換処理を実施します。

 

当ツールの使用方法

基本的な機能、操作方法の説明

1.範囲を指定して一括置換ツールのVBAコードを含んだExcelファイル(***.xlsm)を作成して、そのExcelファイルを開きます。

 

2.検索する文字列と置換する文字列、対象のシート、対象の行/列/範囲をそれぞれ入力します。

対象のシート

検索/置換処理の対象とするシート名を入力してください。
※値がない場合は、全てのシートを対象にして検索/置換します

対象の行/列/範囲

検索/置換の対象となる行、列または範囲を指定してください。
※値がない場合は、全ての範囲を対象にして検索/置換します

記載例
「1:10」 →1行目から10行目までを対象
「A:D」 →A列からD列までを対象
「A1:G5」 →A1からG5の範囲を対象

 

3.一括置換ボタン(2種類)を押した後に、対象のファイルを選択します。

「このファイル内で一括置換実行」を押した場合は、このツールが入っているExcelファイル内を対象として一括置換処理を実行します。

 

「他ファイルを指定して一括置換実行」を押した場合は、ファイル選択ダイアログが開かれて選択したExcelファイルを対象として一括置換処理を実行します。

 

指定した範囲内で指定した「検索する文字列」が「置換する文字列」に置き換わっています。

 

留意事項

当ツールを使用するにあたって、使用可能なルールがありますので、以下を確認してください。

 

ワイルドカードは使用可能だが、正規表現は使用不可

ワイルドカード(*,?)等を使用した前方一致、中間一致、後方一致などは利用可能ですが、正規表現である([ ],^,$,|,+)等は使用できません。

 

ワイルドカードは指定したとおりに置換されていますが、正規表現を使用した場合は、正規表現文字はそのままの文字として完全に一致する文字に対して置換がされます。

 

Option

Option1:条件の適用

それぞれの検索条件に対して、「完全一致のみを対象とする」、「大文字と小文字を区別する」、「半角と全角を区別する」を指定することができます。

 

また、実行した結果として、指定した文字列の置換が全て正常に作成されたかファイル内に指定した文字列に存在しないものがあったか、どちらかのメッセージが表示されますので確認してください。

 

使用する際の事前準備

このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。

 

以下にその手順を説明していきます。

 

準備の手順

1.サンプルのダウンロードはこちら」からサンプル(Excelファイル)をダウンロードし、ファイルを開きます。

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています

 

2.ダウンロードしたExcelファイルの上部にあるリボンの「開発」タブから「visual basic」を選択して、VBE(visual basic for application)を起動させます。

 

リボンに「開発」タブが表示されていない場合は、以下を参照ください。

(参考サイト:記事「Excel VBAを始める前に、最初にやっておくべき初期設定内容はこれ」の『開発』タブを表示させる

 

3.「プロジェクト」に「標準モジュール」を追加して、追加された「Module1」にVBAコードを記載します。

 

VBAのソースコードはこちら

以下のVBAコードをコピーして、「Module1」内のエディターに貼り付けます。

※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます

Option Explicit

'-----(設定値)------------------------
Private Const DATA_START_ROW = 9                '1.検索置換表のデータが開始する行番号

'-----(メッセージ)-------------------
Private Const Msg1 = "一括置換する対象のファイルを選択してください。"
Private Const Msg2 = "このExcelファイル内で一括置換処理が正常に終了しました。"
Private Const Msg3 = "指定したファイル内で一括置換処理が正常に終了しました。"
Private Const WMsg1 = "検索する文字列を設定してください。"
Private Const WMsg2 = "がファイル内に見つかりませんでした。 "
Private Const EMsg1 = "予期せぬエラーが発生しました"
'---------------------------------------

Private TgtBook As Workbook
Private BaseSheet As Worksheet
    
'==========================================================
'このファイル内で一括置換実行ボタンを押した時に実行される処理
'==========================================================
Sub このファイル内で一括置換実行_Click()

    On Error GoTo err
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim msg As String
    Set TgtBook = ActiveWorkbook
    Set BaseSheet = ActiveSheet
    
    '指定した値を検索し、置換処理をする
    Call DoReplace(msg)
    
    If msg = "" Then
        MsgBox Msg2
    Else
        MsgBox msg
    End If
        
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Exit Sub
err:
    MsgBox EMsg1
    
End Sub

'==========================================================
'他ファイルを指定して一括置換実行ボタンを押した時に実行される処理
'==========================================================
Sub 他ファイルを指定して一括置換実行_Click()

    Dim filePath As String
    Dim fileName As String
    Dim msg As String
    Set BaseSheet = ActiveSheet
    
    'ダイアログの表示処理
    filePath = Application.GetOpenFilename(Filefilter:="Microsoft Excelブック,*.xls?,csvファイル,*.csv", Title:=Msg1)

    If filePath <> "False" Then
        
        'ファイル名のみを取得する
        fileName = Dir(filePath)
        Workbooks.Open (filePath), UpdateLinks:=1

        Set TgtBook = ActiveWorkbook
        
        '指定した値を検索し、置換処理をする
        Call DoReplace(msg)
        
        Workbooks(fileName).Close SaveChanges:=True
        
        If msg = "" Then
            MsgBox Msg3
        Else
            MsgBox msg
        End If
        
    'キャンセルが選択された場合はダイアログを閉じる
    Else
        End
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Exit Sub
err:
    MsgBox EMsg1
    
End Sub

'-------------------------
'指定した値を検索し、置換処理をする
'-------------------------
Sub DoReplace(msg As String)

    Dim lastRow As Long
    
    'リストの最終行を取得する
    lastRow = BaseSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    '検索、置換する表内に値がなければメッセージを表示させて処理を終了する
    If lastRow < DATA_START_ROW Then
        MsgBox WMsg1
        End
    End If
    
    Dim tmpSheet As Worksheet
    Dim tgtRng As Range
    Dim i As Long
    
    Dim allMatchFlg As Long                     '完全一致のみ対象にするフラグ
    Dim caseSensitiveFlg As Boolean             '大文字と小文字を区別するフラグ
    Dim byteFlg As Boolean                      '全角と半角を区別するフラグ
    Dim existFlg As Boolean                     '検索ワードが存在するか判断するフラグ
    
    '表内の値の分だけ置換を繰り返す
    For i = DATA_START_ROW To lastRow
        
        Dim searchWord As Variant: searchWord = BaseSheet.Cells(i, 1).Value     '検索ワード
        Dim replaceWord As Variant: replaceWord = BaseSheet.Cells(i, 2).Value   '置換ワード
        Dim tgtSheetNm As String: tgtSheetNm = BaseSheet.Cells(i, 3).Value      '対象とするシート
        Dim tgtRowCol As String: tgtRowCol = BaseSheet.Cells(i, 4).Value        '対象とする行/列/範囲
        
        If BaseSheet.Cells(i, 5).Value <> "" Then allMatchFlg = xlWhole _
            Else allMatchFlg = xlPart                                           '完全一致のみ対象にするフラグ
            
        If BaseSheet.Cells(i, 6).Value <> "" Then caseSensitiveFlg = True _
            Else caseSensitiveFlg = False                                       '大文字と小文字を区別するフラグ
            
        If BaseSheet.Cells(i, 7).Value <> "" Then byteFlg = True _
            Else byteFlg = False                                                '全角と半角を区別するフラグ
            
        existFlg = False                                                        '検索ワードが存在するか判断するフラグ
        
        Select Case searchWord
            Case "^", "$", "?", "*", "+", ".", "|", "{", "}", "\", "[", "]", "(", ")"
            searchWord = "~" & searchWord
        End Select
        
        '対象ファイルの全シートを1つずつループして処理する
        For Each tmpSheet In TgtBook.Worksheets
            
            '対象のシートが空白なら全てのシートを対象にし、対象のシートに値がある場合は指定されたシートのみ処理する
            If (tmpSheet.Name <> BaseSheet.Name) And _
                (tgtSheetNm = "" Or (tgtSheetNm <> "" And tgtSheetNm = tmpSheet.Name)) Then
                
                If tgtRowCol <> "" Then
                    
                    '指定した値が存在するか検索する
                    Set tgtRng = tmpSheet.Range(tgtRowCol).Find(What:=searchWord, _
                        LookAt:=allMatchFlg, MatchCase:=caseSensitiveFlg, matchbyte:=byteFlg)
                    
                    '検索した値を指定した値で置換するが存在するか検索する
                    tmpSheet.Range(tgtRowCol).Replace What:=searchWord, Replacement:=replaceWord, _
                        LookAt:=allMatchFlg, MatchCase:=caseSensitiveFlg, matchbyte:=byteFlg
                Else
                
                    Set tgtRng = tmpSheet.Cells.Find(What:=searchWord, _
                        LookAt:=allMatchFlg, MatchCase:=caseSensitiveFlg, matchbyte:=byteFlg)
    
                    tmpSheet.Cells.Replace What:=searchWord, Replacement:=replaceWord, _
                        LookAt:=allMatchFlg, MatchCase:=caseSensitiveFlg, matchbyte:=byteFlg
                
                End If

                If Not (tgtRng Is Nothing) Then
                    existFlg = True
                End If
            End If
        Next
        
        '指定した値が存在しない場合はメッセージを出力する
        If Not existFlg And InStr(msg, searchWord) = 0 Then
            msg = msg & "「" & searchWord & "」" & WMsg2 & vbCr
        End If
    Next
        
End Sub

 

4.ファイルを保存する際は「Excelマクロ有効ブック」を選択して、保存します。

 

5.右端にある「このファイル内で一括置換実行」と記載された図形に処理「このファイル内で一括置換実行_Click」のマクロを設定します。

 

その右にある「他ファイルを指定して一括置換実行」と記載された図形に処理「他ァイルを指定して一括置換実行_Click」のマクロを設定します。

 

5でマクロ登録した図形にカーソルを当てて、指の形になっていたら正常に設定できています。

 

これで事前準備は完了です。

あとは「当ツールの使用方法」で記載された方法でツールを実行できます。

 

また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が初心者向けでとても参考になると思いますので、良かったらご参照ください。

 

なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。

 

正常に動作しない場合は、コメントいただければ幸いです。
 

また、このような業務効率化できるツールを以下に一覧でまとめてありますので、ご興味のある方はご覧ください。

業務効率化ツールの一覧(まとめ)

操作ごとにまとめて一覧で表示してあります。

Excel VBAを活用した便利マクロツール(まとめ)"Microsoft Excel"を使って業務効率化する際に使用できる便利なマクロツールを一覧で紹介します。 ...

 

当ツールのダウンロードはこちら

下記よりExcelファイルをダウンロードして、記事の途中にありました VBAのソースコードをツール内に組み込んで使用してください。

【Excel VBA】指定した行/列/範囲での複数文字列の一括置換ツール

ツールのダウンロードはこちら

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています

他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います

 

<このツールが『結構使える!』と思ったら、下のグッドボタンを押していただけたら幸いです>

コメントはこちら

  1. より:

    まさに探していたマクロです。
    素晴らしいマクロを公開してくださり、ありがとうございます。

    • RH より:

      管理人のRHです。
      風さん、ツールのご使用とコメントありがとうございます。

      効率化に貢献できたということで嬉しく思います。
      他にも様々なツールがありますので、使用いただければ幸いです

  2. DI より:

    素晴らしいマクロです!仕事で使い業務が楽になりました。ありがとうございます。
    ところで「検索する文字列」を「置換後の文字列」は最大いくつまで登録可能でしょうか。私が検証してみたところ表の外にはみ出ても置換はできるようです。2万件くらいだと置換可能でしたが、10万件くらいだと「検索する文字列を設定してください」と表示が出て動作しませんでした。

    • RH より:

      管理人のRHです。
      DIさん、ツールのご使用とコメントありがとうございます。

      業務が効率化できたようで、とても嬉しく思います。

      「検索する文字列」と「置換後の文字列」の最大件数に関しては特段最大値を検証したことはなかったですが、確認したところ私の端末環境では10万件程度は実行可能でした。

      当ツールは理論上、最大行数(1,048,576行)-1行分※の対象行があっても実行できるプログラムになっていますが、PC性能等により処理完了時間が遅くなりすぎて実質は全て実行できないことがあります。
      (※コード104~110行目の処理より、1,048,576行だと最終行が8行目になり行目を超えないため「検索する文字列を設定…」のエラーが出ます)

      もしDIさんの使用したい場面で不都合がありましたら、言っていただければ幸いです。

COMMENT

メールアドレスが公開されることはありません。 が付いている欄は必須項目です