第12回 直感！スグに使える業務向けVBA汎用プロシージャ(データ範囲の自動検知)

私が編纂・配布させていただいているVBA汎用プロシージャの紹介と簡単な解説になります。
以下のリンクから `.bas` ファイルをダウンロードできます（zipファイル）

https://drive.google.com/file/d/11qEUTsq87UItzoKu2okX5WgEopRercCb/view?usp=drive_link

今回紹介するプロシージャは
| プロシージャ名		| 概要						|
|-------------------------------|-----------------------------------------------|
| ShowAllCells					| 対象シートの全行・前列を表示					|
| AutoCellToArray				| 対象シートのセルの内容を自動で配列に変換			|

の2つになります。今回紹介するプロシージャは第3回で紹介した**CellToArray**の発展版に当たります、もちろん状況に応じて使い分けしていただくものにはなりますがとにかくこれを思いついたときはアドレナリンがめっちゃ流れましたね(笑)
そんな私にとって思い入れのあるプロシージャになっております。

まずはそのサポート用プロシージャとなる**ShowAllCells**について紹介させていただきます。

## ShowAllCells
~~~
Sub ShowAllCells(Optional targetSheet As Worksheet)
'------------------------------------------------------------------------------------------------------------------------------
'　概要　　｜対象シートの全行・全列を再表示し、オートフィルターも解除する
'------------------------------------------------------------------------------------------------------------------------------
'　引数１　｜targetText - 対象文字列(String型)
'------------------------------------------------------------------------------------------------------------------------------
'　実装　　｜Ver.1.1.0(2025/06/14:新規)
'------------------------------------------------------------------------------------------------------------------------------
    
    If targetSheet Is Nothing Then Set targetSheet = ActiveSheet
    
    With targetSheet
        '通常非表示の解除
        .Cells.EntireRow.Hidden = False
        .Cells.EntireColumn.Hidden = False
    
        'フィルター解除
        If .FilterMode Then
            .ShowAllData
        End If
    End With
    
End Sub
~~~

セルの非表示をオンにしていると最終行番号などが正確に取得できないためそれらをすべてオフにするためのプロシージャとなっております。もし見た目を元のままにしたい場合は目的の処理終了後個別にVBAで設定が可能です。本題とはずれてしまうので割愛させていただきますが、ChatGPT等に聞いていただければそこまで大変な作業ではありません。

## AutoCellToArray
さて、こちらがメインディッシュです！
**CellToArray**では配列に格納するセル範囲を指定しておりましたが、**AutoCellToArray**ではセル範囲を指定せずシート上のデータが存在する範囲を自動で検知しすべて配列に格納します。
~~~
Function AutoCellToArray(Optional targetSheet As Worksheet) As Variant
'------------------------------------------------------------------------------------------------------------------------------
'　概要　　｜データが格納されたセル範囲を自動検知し内容を2次元配列として抽出
'------------------------------------------------------------------------------------------------------------------------------
'　引数１　｜targetSheet - 対象のワークシートオブジェクト(規定値:ActiveSheet)(Worksheet型)
'------------------------------------------------------------------------------------------------------------------------------
'　戻り値　｜Variant型 - 指定範囲セルの内容を抽出した2次元配列(インデックスは1開始)
'------------------------------------------------------------------------------------------------------------------------------
'　実装　　｜Ver.1.1.0(2025/06/14:新規)
'------------------------------------------------------------------------------------------------------------------------------
    
    Dim vLoop       As Long '行ループカウンタ
    Dim hLoop       As Long '列ループカウンタ
    Dim getVarEnd   As Long '最終行番号の取得
    Dim getHorEnd   As Long '最終列番号の取得
    Dim startVar    As Long '配列格納開始行番号
    Dim startHor    As Long '配列格納終了行番号
    Dim endVar      As Long '配列格納開始列番号
    Dim endHor      As Long '配列格納終了列番号
    Dim varMax      As Long '最大最終行番号記憶
    Dim customArr   As Variant '格納用配列
    
    If targetSheet Is Nothing Then Set targetSheet = ActiveSheet
    
    'データテーブルの開始・終了列番号の取得
    For hLoop = targetSheet.Columns.Count To 1 Step -1
        getVarEnd = Cells(targetSheet.Rows.Count, hLoop).End(xlUp).Row
        If getVarEnd = 1 And targetSheet.Cells(1, hLoop) = "" Then getVarEnd = 0
        If getVarEnd > 0 And endHor = 0 Then endHor = hLoop
        If getVarEnd > 0 And endHor <> 0 Then startHor = hLoop
        If getVarEnd > varMax Then varMax = getVarEnd
    Next hLoop
    
    'データテーブルの開始・終了行番号の取得
    For vLoop = varMax To 1 Step -1
        getHorEnd = Cells(vLoop, targetSheet.Columns.Count).End(xlToLeft).Column
        If getHorEnd = 1 And targetSheet.Cells(vLoop, 1) = "" Then getHorEnd = 0
        If getHorEnd > 0 And endVar = 0 Then endVar = vLoop
        If getHorEnd > 0 And endVar <> 0 Then startVar = vLoop
    Next vLoop
    
    '配列の初期化
    ReDim customArr(endVar - startVar + 1, endHor - startHor + 1)
    
    'セル内容の抽出
    For vLoop = 1 To endVar - startVar + 1
        For hLoop = 1 To endHor - startHor + 1
            customArr(vLoop, hLoop) = targetSheet.Cells(vLoop + startVar - 1, hLoop + startHor - 1)
        Next hLoop
    Next vLoop
    
    AutoCellToArray = customArr
    
End Function
~~~
例えば
![Excel-12-1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4046902/d2fc75f4-2549-40e0-abcc-7cd8a480594d.png)

このような表があったときに
~~~
call AutoCellToArray(Sheet1)

または

call AutoCellToArray
~~~

で

![Excel-12-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4046902/b08a26b6-0f3f-48df-8de9-310825075d90.png)

赤枠部の範囲を並列に格納してくれます。
もちろん既存の**CellToArray**と目的によって使い分けていただく必要がありますが、直感的にシステムを構築できるようにするための選択肢が広がったかと思います！

## 直感！VBAシリーズ記事一覧
もしよろしければ他の記事もご覧ください！

https://qiita.com/Reko/items/3e482f9a73353e964f00

https://qiita.com/Reko/items/af042ae33cdc01e1dc96

https://qiita.com/Reko/items/9e211c9c496e12283f33

https://qiita.com/Reko/items/c2dab7b0e8207f8049df

https://qiita.com/Reko/items/b5abcdf614cef5d4073a

https://qiita.com/Reko/items/3451339e723a34ac7897

https://qiita.com/Reko/items/40c66556ac51a8006ea6

https://qiita.com/Reko/items/0269b2bece07a675e797

https://qiita.com/Reko/items/e2d0d0bf2dea4feface1

https://qiita.com/Reko/items/b995c3da88d25f6e0104

https://qiita.com/Reko/items/bc97d79599cfd431b2f3

https://qiita.com/Reko/items/1731aaddfc6e50c209df

https://qiita.com/Reko/items/e8253ac68ccafba44ad3