複数階層にある全ファイルのリストを自動で作成する方法③（Excelマクロで自動整形）

細かいことは不要で、純粋にコード情報が欲しい方は、

[↓をコピー]＞＞[Excelを開いて[alt]+[F11]（画面がグレーでしたら、[挿入]＞＞[標準モジュール]）]＞＞[貼り付け]＞＞[マクロ]＞＞[実行]で整形完了です！

     Sub 情報の整理()
     '使用する文字の設定--------------------------------------------
      Dim tmp As Variant
      Dim lRow, iCol As Long
      Dim h, i As Long
      Dim hypling As Hyperlink
      Dim str As String
      
     'ココから実施内容----------------------------------------------
     '行数を数える。
      lRow = Cells(Rows.Count, 2).End(xlUp).Row
      
     '不要な情報（行）の削除
       Application.ScreenUpdating = False    '作業中の画像変更無し。
       For i = lRow To 3 Step -1
           If _
             InStr(Cells(i, 2).Value, ".lnk") <> 0 Or _
             InStr(Cells(i, 2).Value, ".db") <> 0 Or _
             InStr(Cells(i, 2).Value, "個のファイル") <> 0 Or _
             InStr(Cells(i, 2).Value, "ドライブ") <> 0 Or _
             InStr(Cells(i, 2).Value, "ボリューム") <> 0 Or _
             InStr(Cells(i, 2).Value, "バイトの空き領域") <> 0 Or _
             InStr(Cells(i, 2).Value, "ファイルの総数") <> 0 Or _
             Cells(i, 2).Value = "" Then
               Range(i & ":" & i).Delete
           End If
       Next i
       
     '不要な文字の削除
      For i = lRow To 3 Step -1
       If InStr(Cells(i, 2).Value, " のディレクトリ") <> 0 Then
         Cells(i, 2).Value = Replace(Cells(i, 2), " のディレクトリ", "")
       End If
      Next i
       
     '文字列の分割
     '空白区切りでセルを分割して定置
      For i = lRow To 3 Step -1
        tmp = Split(Cells(i, 2), " ")
        For h = LBound(tmp) To UBound(tmp)
          Cells(i, h + 2) = tmp(h)
        Next h
      Next i
      
     '不要な空白のセルの削除
      For i = lRow To 3 Step -1
        iCol = Cells(i, Columns.Count).End(xlToLeft).Column
        For h = iCol To 2 Step -1
          If Cells(i, h) = "" Then
            Cells(i, h).Delete shift:=xlToLeft
          End If
        Next h
      Next i
      
     '相対パスでハイパーリンクを設定する場合は、以下をコメント解除
     '（文頭の『'』を削除）
   '   For i = lRow To 3 Step -1
   '     If InStr(Cells(i, 2).Value, ":\") <> 0 Then
   '       str = Cells(i, 2).Value
   '         Cells(i, 2).Value = Replace(Cells(i, 2), Left(str, 3), "")
   '     End If
   '   Next i
      
     '各ディレクトリへのハイパーリンクを設定
      Application.ScreenUpdating = False
      With ActiveSheet.Hyperlinks
        For i = lRow To 3 Step -1
          If InStr(Cells(i, 2).Value, "\") <> 0 Then
            .Add Anchor:=Cells(i, 2), Address:=Cells(i, 2).Value
          End If
        Next i
      End With
      
      Application.ScreenUpdating = True    '作業中の画像再表示。
   End Sub
  

1. 対象となる行数（最後の少数）を計数する。
2. 不要な行を削除する。
3. 不要な文字を削除する。
4. スペース区切りで文字列を分ける。
5. ハイパーリンクをつける。

今回のケースでは、行末を数えたい対象に、空白行があるので上から下に検索して、最後の行を抽出しようとすると、最初の空白行の数が抽出されてしまいます。

そこで、マクロで最終行を取得する際には、対象とする列の一番最後の行数から、上方向に検索して最後の行数を抽出します。

使うコードは、以下となります。

      
      lRow = Cells(Rows.Count, 2).End(xlUp).Row
      
    

このコードは、①『Cells(行数，列数)』、②『End（向き）』、③『Row』の３つで構成されていて、
①はあくまでもセルの場所を示していて、②はそこからどの向きに動くか・止まるかを指示し、③行の数を表示する。と言う内容です。

このうち、『Rows.Count』はシートの行数（Excel2007以降であれば、1048576行）、『xlUp』は上向きを示しています。

従いまして、この関数を日本語で訳すとすると…
『Cells(Rows.Count, 2).End(xlUp).Row』
『セルB1048576から上方向に動いて最初に止まった行』

という意味になります。

不要な行は、「『For〜Next』で全ての行を調べ、不用な行に含まれる文字があれば、削除する。」といった作業をします。

具体的には、↓となります。

    
    For i = lRow To 3 Step -1
      If _
        InStr(Cells(i, 2).Value, ".lnk") <> 0 Or _
        InStr(Cells(i, 2).Value, ".db") <> 0 Or _
        InStr(Cells(i, 2).Value, "個のファイル") <> 0 Or _
        InStr(Cells(i, 2).Value, "ドライブ") <> 0 Or _
        InStr(Cells(i, 2).Value, "ボリューム") <> 0 Or _
        InStr(Cells(i, 2).Value, "バイトの空き領域") <> 0 Or _
        InStr(Cells(i, 2).Value, "ファイルの総数") <> 0 Or _
        Cells(i, 2).Value = "" Then
          Range(i & ":" & i).Delete
      End If
    Next i
    
  

これは、情報の載っている最後の行から、上方向に１つずつ検索し、もし（if）、不用な行にある文字が０でなければ（InStr(セルの値, "不要文字") <> 0）、その行を削除する（Range(i & ":" & i).Delete）。

といった内容です。

このコードでは、少しでも記述する行数を減らすために、『Or』で条件を繋いでいます。

文字を削除する際の書き方は、基本的に行の削除の方法と同じです。

ただ、文字の削除については、『置換』機能を使用します！置換は『Replace(対象セル, "対象文字列", "置換後の文字列")』でできますので、具体的には↓のようなコードです☆

    
      For i = lRow To 3 Step -1
        If InStr(Cells(i, 2).Value, " のディレクトリ") <> 0 Then
          Cells(i, 2).Value = Replace(Cells(i, 2), " のディレクトリ", "")
        End If
      Next i
      
    

続いては、文字列を空白の場所で区切って、それぞれのセルに分けていこうと思います！

こちらも、全行に実施したいので、For～Nextで記述しますが、具体的には、

• Split(対象セルの位置, 区切り文字)を使って、分割した文字列（配列）を下記の場合はtmpに保存
• tmpの文字の数について、LBound、UBound関数を使って抽出し、Forでどこからどこまで記述する必要があるかをExcelに教える
• その数分だけ、各セルに入れ込む。

といった作業をしていきます。

実際のコードは以下の通りです！

      For i = lRow To 3 Step -1
        tmp = Split(Cells(i, 2), " ")
        For h = LBound(tmp) To UBound(tmp)
          Cells(i, h + 2) = tmp(h)
        Next h
      Next i
     コード

続いては、個人的にはこのファイルリストで最も重要なハイパーリンクの設定です！

ハイパーリンクは、Hyperlinks.Add(Anchor:=設定するセルの位置, Address:=設定するURL）で設定できます。

そこで、今回具体的に設定したコードは以下の通りです！

      With ActiveSheet.Hyperlinks
        For i = lRow To 3 Step -1
          If InStr(Cells(i, 2).Value, "\") <> 0 Then
            .Add Anchor:=Cells(i, 2), Address:=Cells(i, 2).Value
          End If
        Next i
      End With