VBA｜SpecialCells(xlCellTypeVisible)がエラーの場合の原因・対処方法

SpecialCells(xlCellTypeVisible)メソッドを使っていますか？

SpecialCells(xlCellTypeVisible)メソッド 使うとすべての可視セルを取得することができる便利メソッドですが、エラーになってしまうケースも多いのです。

今回は SpecialCells(xlCellTypeVisible)メソッドのエラー原因・対処方法について徹底的に説明していきます。

参考：Excel VBA入門 その48：SpecialCellsでエラー「該当するセルが見つかりません。」を回避する方法

SpecialCells(xlCellTypeVisible)がエラーの場合の原因・対処方法

今回は、下のエクセル表でフィルターをかけ、そのときの可視セルの行数をメッセージボックスで表示させる場合のエラーについて説明していきます。

SpecialCells(xlCellTypeVisible)を用いたときの正しいコード

まずは、SpecialCells(xlCellTypeVisible)を使用したときの正しいコードを紹介します。

Sub 正しいコード()
    Dim visible_cell As Long
    visible_cell = Sheets("Sheet1").Range("A2:D11").Columns(1).SpecialCells(xlCellTypeVisible).Count
    MsgBox "可視セル行数：" & visible_cell
End Sub

実行結果は、可視セルの行数を数えるので「６」と表示されます。

次は想定されるエラーの原因・対処方法を説明していきます。

エラーその１：オーバーフローになる

オーバーフローになるときのコード例は以下のようなものです。

Sub オーバーフローする間違ったコード()
    Dim visible_cell As Long
    visible_cell = Sheets("Sheet1").Cells.SpecialCells(xlCellTypeVisible).Count　'←数える範囲を指定していない
    MsgBox "可視セル行数：" & visible_cell
End Sub

オーバーフローの原因は、３行目のSheets(“Sheet1”).Cells.SpecialCells(xlCellTypeVisible).Countの、Cellsの部分です。

正しいコードでは、Sheets(“Sheet1”).Range(“A2:D11”).Columns(1).SpecialCells(xlCellTypeVisible)のように 、可視セルを数える範囲をRange(“A2:D11”) .Columns(1) のように指定しています。

可視セルを数える範囲を指定していない間違ったコード例では、本来範囲を指定するべきところを単に「Cells」としているため、シート全体における可視セルを数えてしまいオーバーフローになってしまうのです。

解決策としては、可視セルを数える範囲を指定するために、Range(△：□).columns(●)とするか、どうしてもCellsを使いたいのならばRange(Cells(△)：Cells(□)).columns(●) のようにすることです。

エラーその２：可視セルの行数と結果が一致しない

よくあるエラーとして、可視セルの行数と実行結果が一致しないことがあります。

下の表は「くだもの」でフィルターをかけたもので、可視セルの行数は「４」になるはずが実行結果は「２」になってしまう・・・というものです。

可視セルの行数と実行結果が一致しないときのコード例は以下のようなものです。

Sub 可視セルと実行結果が一致しない間違ったコード()
    Dim visible_cell As Long
    visible_cell = Sheets("Sheet1").Range("A2:D12").Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count '←Rows.Countは、空白行と空白行の間の行をカウントする
    MsgBox "可視セル行数：" & visible_cell
End Sub

可視セルの行数と実行結果が一致しない原因は、３行目の～.SpecialCells(xlCellTypeVisible).Rows.Countの「Rows」が原因です。

正しいコードでは、～.SpecialCells(xlCellTypeVisible).Countとしていて、Rowsを入れていません。

Rows.Countは、データが入っている行数を取得するものですが、カウントの方法としてはデータが入っているセルから次の空白行を数えているものなのです。

つまり、セルを取得するというのは、データが入っているセルを起点として降下していき、空白セルを見つけると終了します。したがって、空白セル行より下にデータが入っているセルがあっても取得されません。

下の表はフィルターを解除したものです。このあとに「くだもの」でフィルターをかける際に、２～３行目は取得されますが、４行目は「野菜」なのでフィルターで除外され空白行とみなされます。

空白行以降は取得されなくなるので、６行目、８行目のくだものは取得されなくなります。

フィルターを「くだもの」でかけると、可視セル行数は「４」ですが、実際には２～３行目の行しか取得されず、実行結果は「２」と表示されることになります。

以上のようにRows.Countはただ単に行数を取得するのではなく、空白セルがあるとそこで取得終了してしまうものなのです。（※この動作をメリットとして使うこともあります。）

解決策としては、 ～.SpecialCells(xlCellTypeVisible).Rows.Countの「Rows」を削除して、 ～.SpecialCells(xlCellTypeVisible).Countと記述することです。

参考：Excel VBA入門 その48：SpecialCellsでエラー「該当するセルが見つかりません。」を回避する方法