[[20180605112843]]　『マクロでテーブル内の可視セルの取得ができない』（スマトラ）　ページの最後に飛ぶ

『マクロでテーブル内の可視セルの取得ができない』（スマトラ）

お世話になっております。

テーブル機能のデータ範囲取得時のエラーについて

（シート構成）
・A1:DF最終行をEXCELのテーブル機能を使用して表を作成
・1行目:項目行

（コードの内容）
下記のコードは、表の2行目～データのある範囲を取得し
ListBoxに読み込むようにしてあります。

（問題となっているのは・・・）

テーブル内の「最終行」を削除した際に、データ範囲が正しく読み込めず
どうも、最終行下の空白行まで認識しているようです。

EXCEL側のテーブル範囲を確認すると空白行を除くデータ範囲を読み込んでるようなのですが

マクロ実行時に空白行に対して行の挿入削除すると誤認してしまうようです。

試しに

99行目まで値があったとして、100行目の空白行を削除→下記マクロを実行するとエラーとなります。

エラ^内容　実行時エラー91　オブジェクト変数またはwithブロック変数が設定されていません。

アドバイスの程よろしくおねがいします。

Sub ListBox2への可視セルデータセット()

    Dim rngFilter As Range
    Dim rngKeyCol As Range
    Dim c, c2 As Range
    Dim i As Long
    Set rngFilter = Sheets("台帳").AutoFilter.Range
    With rngFilter.Columns(1)
        On Error Resume Next
        Set rngKeyCol = Intersect(.Cells, Offset(1)).SpecialCells(xlCellTypeVisible)
    End With
    Me.ListBox2.Clear
    If Not rngKeyCol Is Nothing Then '値がある場合
        For Each c In rngKeyCol
            With Me.ListBox2
                .AddItem
                i = .ListCount - 1
                .List(i, 0) = c.Range("B1").Value    '1列目　絆ID
                .List(i, 1) = c.Range("P1").Value    '2列目　名
                .List(i, 2) = c.Range("Q1").Value    '3列目　カナ
            End With
        Next
        'Labelの表示設定
        Label_選択数.Caption = "全" & ListBox2.ListCount & "件"
        Label_リスト数.Caption = ""    '一旦クリア
    Else '値がない場合
        Me.ListBox2.AddItem ""
        Me.ListBox2.List(0, 0) = ""
        Me.ListBox2.List(0, 1) = "該当なし"
    End If
End Sub

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 対症療法的な対策例として、

 >c.Range("B1").Value    '1列目　絆ID
 コイツが値を持ってるかどうかを
 逐一判定しながらListBox2にAddItemしていく

 とかどうでしょ?

    For Each c In rngKeyCol
    	If Len(c.Range("B1").Value) Then '[絆ID]が1文字以上だったら
            With Me.ListBox2
                'リスト追加処理
            End With
        End If
    Next

（白茶） 2018/06/05(火) 13:38

（追記）

    With rngFilter.Columns(1)←ここでエラーが出ているようです。

        On Error Resume Next
        Set rngKeyCol = Intersect(.Cells, .Offset(1)).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0　←入力忘れていました。
        rngKeyCol = c2.A
（スマトラ） 2018/06/05(火) 14:07

 「そこかよ!!」って声に出ちゃいました^^;
 スミマセンが、私では役者が足りない様です。

（白茶） 2018/06/05(火) 14:34

　＞With rngFilter.Columns(1)←ここでエラーが出ているようです。
そこでエラーが出るなら、
　＞Sheets("台帳").AutoFilter.Range
これがNothingを返してるのかと思うのですが、
そんなことが有り得るか、
状況を再現出来ないとデバッグできないかなと思います。

そこで、
データーが10行×5列くらいで、
状況を再現できるシートのイメージを提示してもらうことは可能でしょうか？

ちょっと、、、、

　＞EXCEL側のテーブル範囲を確認すると空白行を除くデータ範囲を読み込んでるようなのですが
　＞マクロ実行時に空白行に対して行の挿入削除すると誤認してしまうようです。
↑シート上のセル範囲をテーブル機能の設定をしていることですか？
それとも単に表をテーブルと表現されているのかどちらですか？

あと、行の挿入削除はどういう操作で行われてますか？

あと、データ中に空白セル（あるいは空白行）はよく出てきますか？

（まっつわん） 2018/06/05(火) 14:52

あぁ、これでそうでしょうか？

  >  Set rngFilter = Sheets("台帳").AutoFilter.Range　'←ここでエラー
  >  With rngFilter.Columns(1)
  >      On Error Resume Next
  >      Set rngKeyCol = Intersect(.Cells, Offset(1)).SpecialCells(xlCellTypeVisible)
  >  End With

    Set rngFilter = Sheets("台帳").ListObjects(1).DataBodyRange
    On Error Resume Next
    Set rngKeyCol = rngFilter.Columns(1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

表の外のセルを選択していると
　＞マクロ実行時に空白行に対して行の挿入削除すると
AutoFilter.Range
がエラーになるようです。

シート上の「テーブル」は、
VBAではListObjectsと表現しますので、
そちらからセル範囲の情報を得てください。

（まっつわん） 2018/06/05(火) 15:04

コメント返信：

[ 一覧(最新更新順) ]

[[20180605112843]] 『マクロでテーブル内の可視セルの取得ができない』（スマトラ） ページの最後に飛ぶ

[[20180605112843]]　『マクロでテーブル内の可視セルの取得ができない』（スマトラ）　ページの最後に飛ぶ