セルの最終行を取得して検索した値を入力する

エクセルVBAで「Cells(Rows.Count, 1).End(xlUp)」を使って、A列のセルの最終行から上方向に向かってデータが入力されている最後のセルを取得した後「Offset(1, 0).Value」で一つ下のセルに検索した値を入力する方法を紹介します。

この方法を使うと、最終行を常に把握しその次の行に新しいデータを追記していくので正確なセルの入力が可能になります。

先ずはエクセルシート1に左のようにセルのA列とB列にファイル名とセルの値を入力する欄を作成します。

次にVBEの画面から「挿入」→「標準モジュール」で標準モジュールを作成します。

そして下のコードをコピーして標準モジュールのModule1に貼り付けます。

Sub データが入力されているセルの最終行を取得して検索した値を入力する()

   Dim file_name As String
   Dim folder_name As String
   folder_name = ThisWorkbook.Path & "\検索フォルダ\"                       '検索するフォルダ名を変数に格納します。
    
   file_name = Dir(folder_name & "*.xlsx")                                                   'Dir関数で検索します。
    
   Do While file_name <> ""                           'フォルダ内にエクセルのファイルが見つかる間は処理を行います。
    
      Workbooks.Open folder_name & file_name                                                  'ファイルを開きます。
             
      With ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) 'セルにデータがある最終行を取得します。
                
        .Offset(1, 0).Value = file_name
        .Offset(1, 1).Value = Workbooks(file_name).Worksheets("Sheet1").Range("A1").Value
          
      End With
          
      Workbooks(file_name).Close savechanges:=False                                           'ファイルを閉じます。
        
      file_name = Dir()                                                     'フォルダ内の次のファイルを検索します。
        
   Loop
    
End Sub

ファイルの保存

名前を付けて保存でファイル名を「マクロを実行するファイル.xlsm」として保存します。

検索フォルダの作成

ファイルを検索する「検索フォルダ」を作成して中に5つのファイルを入れます、ファイルAからファイルEのセルA1にはA～Eの文字を順番に入れて保存しておきます。

「検索フォルダ」と「マクロを実行するファイル.xlsm」を同じフォルダに保存します。ThisWorkbookで保存場所を自動で取得するのでフォルダ名は自由に付けて差し支えありません。

プログラムの実行

マクロを実行するファイル.xlsmのVBEの標準モジュールのコードを表示し「Sub/ユーザーフォームの実行」ボタンを押して標準モジュールのプログラムを実行します。

プログラムが終了するとエクセルシートのA列に「検索フォルダ」の中にあったファイル名とB列には「セルA1」に入っていた値が表示されます。

エクセルシートに表を作成するにはCells(Rows.Count, 1).End(xlUp)を使ってデータが入力されている最後のセルを取得する方法が必修なので、今回は実例を基にして作成方法を書かせていただきました。

エクセルVBAで最終行以降に自動で入力していく方法は、誤って既存のデータを上書きしてしまう事がないので簡単なデータベース作成やテーブルの追記などに役立つと思いますのでぜひご活用ください。