[[20180518144122]] 『可視セルの空白セルをカウントしたい:VBA』(とらっきー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『可視セルの空白セルをカウントしたい:VBA』(とらっきー)

タイトルの通りなのですが、12万行×120列ほどの表があり、
E列でオートフィルタをかけています。
その際に、R列からDH列までに空白がないか確認したいです。

もし空白があれば「○個空白有り」とメッセージで表示、
そのセルをすべて選択させたいです。(例え1万セルあろうとも・・)

WorksheetFunction.CountBlankでは非可視セル範囲内のものも
数えてしまう様です。

なにかいい方法ないでしょうか?

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 データ量多いので、待ち時間が如何なものかと心配しつつ、
 とりあえずシート保護は掛かってない前提でSpecialCellsに頼って
 先に該当Rangeを確定してしまい、個数メッセージは.Cells.Countで。
 という案です。

    Sub Test()
        Const AREA1 As String = "R:DH"
        Dim r As Range
        With ActiveSheet
            If Not .AutoFilterMode Then Exit Sub
            Set r = Intersect(.AutoFilter.Range, .Range(AREA1))
        End With
        If r Is Nothing Then Exit Sub
        Set r = r.SpecialCells(xlCellTypeVisible)
        If Not r Is Nothing Then Set r = r.SpecialCells(xlCellTypeBlanks)
        If Not r Is Nothing Then
            r.Select
            MsgBox r.Cells.Count
        End If
    End Sub

 フィルタの状況によって所要時間にかなりの差が出そうです。
 元々フィルタで表示されている行数が数千行程度であれば、待たされる時間はさほど気にならないでしょうが、
 試しに手許にあった20万8千行のデータでフィルタOff状態で使ってみたら結構待たされました。(目算約30秒)

(白茶) 2018/05/18(金) 16:11


 あ、しまった。
 該当が無かった場合のことを考慮してませんね。

(白茶) 2018/05/18(金) 16:16


 とりあえず該当なしエラー回避版です^^;

    Sub Test()
        Const AREA1 As String = "R:DH"
        Dim rVis As Range, rBlank As Range
        With ActiveSheet
            If Not .AutoFilterMode Then Exit Sub
            Set rVis = Intersect(.AutoFilter.Range, .Range(AREA1))
        End With
        If rVis Is Nothing Then Exit Sub
        On Error Resume Next
        Set rVis = rVis.SpecialCells(xlCellTypeVisible)
        If Not rVis Is Nothing Then Set rBlank = rVis.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rBlank Is Nothing Then
            rBlank.Select
            MsgBox rBlank.Cells.Count
        End If
    End Sub

(白茶) 2018/05/18(金) 16:22


白茶様ありがとうございます。

.SpecialCells(xlCellTypeVisible)を変数に格納して、.SpecialCells(xlCellTypeBlanks)で先に選択、
選択されたセル数をカウント、ということですね。

大変勉強になりました。
おかげで無事件数を出せました!

私の環境では数秒で終わりました!
(とらっきー) 2018/05/18(金) 16:30


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.