可視セルのみを二次元配列に格納するには？（Tips-2）

VBAの勉強を始めて、これまでに習得したことや、思いついたアレやコレをメモっておきます。

結論から言うと、あらかじめ用意されているプロパティやメソッドなどで、可視セル範囲のみをうまいこと二次元配列に格納することはできないようです。（あくまでも、浅学な私が調べたところでは、です）

下記のような、3行目と3列目を非表示にした範囲から、可視セルのデータのみを配列に格納してみましょう。

f:id:kouten0430:20180526120458j:plain

まず、一番最初に思いつくのは、
配列 = Selection.SpecialCells(xlCellTypeVisible)
です。
Selection.SpecialCells(xlCellTypeVisible) は、選択範囲の可視セルのみを取得するプロパティなので、これであっさり、可視セルのみを二次元配列に格納できる！と思ったのですが･･････どうもうまくいきません。

ローカルウィンドウで配列の中身を確認すると、このように、1,2,6,7しか取り込まれていません。あれれ？

f:id:kouten0430:20180526120553j:plain

さきほどの非表示の行、列を再表示してみましょう。（3行目と、3列目に色をつけてみました）

f:id:kouten0430:20180526120636j:plain

この非表示の行、列を境に右側、下側が配列に取り込まれていないようです。
配列ではなく、Range型のオブジェクト変数に代入してローカルウィンドウで変数の中身を見ると分かるのですが、

この場合、Areas(範囲のひとかたまり）が4つ存在していることが分かります。

f:id:kouten0430:20180526120713j:plain

この非表示セルで分断された、4つの範囲のことですね。

f:id:kouten0430:20180526120748j:plain

Selection.SpecialCells(xlCellTypeVisible)を受け取る変数が配列だと、左上のAreasしか代入できない仕様のようです。

でも、コードをうまいこと組み合わせれば、なんとかできるハズ！ということでやってみました。

可視セル範囲の行数、列数をカウントして、配列の要素数を再定義し、For ～Nextで列・行方向に非表示セルを除いてデータを取り込む作戦です。

しかし、ここでまたAreas問題にぶち当たります。

Selection.SpecialCells(xlCellTypeVisible).Rows.Count で、行数をカウント
Selection.SpecialCells(xlCellTypeVisible).Columns.Count で、列数をカウント
しようとするも、左上のAreasのぶんしかカウントしてくれません。

う～ん。

しかたないので、苦肉の策（？）で、このようにカウントの仕方を変えます。
Selection.Rows(1).SpecialCells(xlCellTypeVisible).Cells.Count で、選択範囲の1行目の可視セル数をカウント
Selection.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count で、選択範囲の1列目の可視セル数をカウント

ふぅ･･････。これでやっと、可視セル範囲の行数、列数をカウントし配列の要素数を再定義することができました。
ここまでくれば、あとはわりとカンタンな気がします。
For ～Next jで列のデータを、For ～Next iで行のデータを順次、二次元配列に取り込みます。

f:id:kouten0430:20180526121109j:plain

コードはコレ

    y = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
    x = Selection.Rows(1).SpecialCells(xlCellTypeVisible).Cells.Count
    
    ReDim Tdim(1 To y, 1 To x)
    
    dim1 = 1
    dim2 = 1
        
    For i = Selection.Row To Selection.Rows(Selection.Rows.Count).Row
        For j = Selection.Column To Selection.Columns(Selection.Columns.Count).Column
            If Rows(i).Hidden = False And Columns(j).Hidden = False Then '非表示セルは処理しない
                Tdim(dim1, dim2) = Cells(i, j).Value
                    dim2 = dim2 + 1
                    If dim2 > x Then
                        dim2 = 1
                        dim1 = dim1 + 1
                    End If
            End If
        Next j
    Next i

重要なのは、表示されている列・行のみに処理を行うということです。合わせて、処理ごとに、一次元の要素数に +1 していきます。
jが右端まで来ると、一次元の要素数を1にリセットし、二次元の要素数を繰り上げます（カンタンに言うと、一行下がって左端に戻るようなことです）
これを、ｊとiが最後の値になるまで繰り返します。（この繰り返しの中で、Ifの条件を満たす回数と、配列の再定義した要素数が一致することがミソです）

ローカルウィンドウで結果を見てみます。

f:id:kouten0430:20180526121335j:plain