VBA・マクロツール

【知らなくても使える】指定した行/列/範囲で複数の指定した文字列を一括置換するマクロツール(Excel VBA)

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

 

指定した範囲に絞って検索&置換を行いたい場合は、以下のような作業を行っていないでしょうか。

  1. 対象の範囲のセルを選択する。
  2. 『Ctrl + H』を押して、検索と置換のダイアログを開く。
  3. 検索する文字列、置換後の文字列などをそれぞれ入力して「OK」をクリックする。

上記の作業を、複数の文字列を置換したい場合は、何度も作業を行う必要があります。

このような大変な作業を自分の時間を削って、何度も何度も繰り返し行っていては、時間が非常にもったいないです。

 

そんな場面で、無駄な作業が効率化できる、エクセルの表に検索する文字列と置換する文字列をいくつも指定して実行すると、指定した全ての文字列を全て一括で置換できる『指定した行/列/範囲で複数の指定した文字列を一括置換するマクロツール』をExcel VBAで作成しましたので、是非ご活用頂けたらと思います。

 

当記事にはマクロの元になるExcelファイルがダウンロード可能で、VBAのコードも記事内に記載がありますので、Excel VBAやマクロが良く分からないという方でもすぐに使用できます

当ツールを使用すれば定例業務の業務効率化が図れるかと思いますので、是非ご活用頂けたらと思います。

 

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

別ファイル内の複数文字列を一括で置換するツール(Microsoft Excel) 本記事では、最も有名でかつ使用頻度が高い表計算ソフトの「Microoft Excel」で使用できる『複数文字列を一括で置換するマ...
ツールの更新履歴

(2023年1月13日:更新)

・全体的なUIを変更しました。

(2023年8月1日:更新)

・CSVファイルを置換した際に、yyyy/m/dで入力されていたデータがm/d/yyyyに変わってしまう現象を解消(yyyy/m/dのまま置換)

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

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

 

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 DATA_START_COLUMN = "B"           '2.検索置換表のデータが開始する列アルファベット

'-----(メッセージ)-------------------
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()

    On Error GoTo err
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    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)
        
        TgtBook.SaveAs fileName:=fileName, Local:=True
        Workbooks(fileName).Close
        
        If msg = "" Then
            MsgBox Msg3
        Else
            MsgBox msg
        End If
        
    'キャンセルが選択された場合はダイアログを閉じる
    Else
        End
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
    Exit Sub
err:
    MsgBox EMsg1
    
End Sub

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

    Dim lastRow As Long
    Dim startDataCol As Long
    
    'リストの最終行を取得する
    lastRow = BaseSheet.Cells(Rows.Count, DATA_START_COLUMN).End(xlUp).Row
    startDataCol = Columns(DATA_START_COLUMN).Column
    
    '検索、置換する表内に値がなければメッセージを表示させて処理を終了する
    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, startDataCol).Value     '検索ワード
        Dim replaceWord As Variant: replaceWord = BaseSheet.Cells(i, startDataCol + 1).Value '置換ワード
        Dim tgtSheetNm As String: tgtSheetNm = BaseSheet.Cells(i, startDataCol + 2).Value      '対象とするシート
        Dim tgtRowCol As String: tgtRowCol = BaseSheet.Cells(i, startDataCol + 3).Value        '対象とする行/列/範囲
        
        If BaseSheet.Cells(i, startDataCol + 4).Value <> "" Then allMatchFlg = xlWhole _
            Else allMatchFlg = xlPart                                           '完全一致のみ対象にするフラグ
            
        If BaseSheet.Cells(i, startDataCol + 5).Value <> "" Then caseSensitiveFlg = True _
            Else caseSensitiveFlg = False                                       '大文字と小文字を区別するフラグ
            
        If BaseSheet.Cells(i, startDataCol + 6).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」のマクロを設定します。

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

あとは「基本的な機能、操作方法の説明」で記載された方法でツールを実行できます。

 

なお、当マクロの開発環境は、OS:Windows10 、Excelソフトウェア:Microsoft Office 365となっており、当環境では動作確認ができていますが、他の環境で正常に動作するかは確認できていません。

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

 

 

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

 

自力で業務効率化できるツール等を作成する場合は、オンラインITスクール」を利用するとモチベーションを保ちつつ、効率的に学習とアウトプットができると思います。

\ 今なら1か月間全額返金保証!! /

上記の「侍テラコヤ」月額2,980円~ という日本最安級の料金でプログラミング学習ができ、今なら初めての方でも安心できる「1か月全額返金保証」があります

自分に合わないと感じた場合は返金してもらえるため、ノーリスクで試すことができます。是非一度体験してみることをオススメします ^ ^

 

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

下記より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さんの使用したい場面で不都合がありましたら、言っていただければ幸いです。

  3. K・I より:

    とても便利に使わせていただいてます!
    ありがとうございます!

    1点だけ、CSVファイルを置換した際に、yyyy/m/dで入力されていたデータがm/d/yyyyに変わってしまう為、毎回手で修正しているのですが、何か良い方法があれば、教えていただけると幸いです。

    • RH より:

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

      CSVファイルを置換する際のことを考えていませんでした、ご指摘ありがとうございます。
      CSVファイルを置換した際にyyyy/m/d形式のまま置換するように改修しましたので、再度ツールをご使用いただきご確認ください。

      • K・I より:

        管理人様

        ありがとうございます!
        早速使わせていただきました。

        作業効率がとても良くなり助かりました。

        • RH より:

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

          作業効率が良くなり良かったです。
          またなにかありましたらコメントください。^^