『オートフィルタで絞り込んだ結果のみをリストボックスに表示』（toto）

 「ToDoリスト」というシートで、ユーザーフォームで検索条件を指定し、その条件で
 オートフィルタで絞り込んで、絞り込んだ結果のみをリストボックスに表示しようと
 していますがうまくいきません…


 Public ySh As Worksheet
 Public lRow As Long
 Public Kensaku As Variant

 Dim z As Long
 Dim i As Long
 Dim c As Range

     Set ySh = ThisWorkbook.Worksheets("ToDoリスト")
    lRow = ySh.Range("B" & ySh.Rows.Count).End(xlUp).Row

 　　Kensaku = StrConv(UCase(Text1), vbNarrow)　　'Text1(テキストボックス)に検索条件を入力

     ListBox1.Clear
    
 　　'範囲はA列〜F列、検索条件はB列の値を対象
    ySh.Range("A2:F" & lRow).AutoFilter Field:=4, Criteria1:=Kensaku
    z = ySh.Range("B" & ySh.Rows.Count).End(xlUp).Row

    For Each c In ySh.Range("A2:F" & z).SpecialCells(xlCellTypeVisible)

    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "30;50;50;70;100;150"
        .RowSource = "ToDoリスト!A3:F" & z
    End With
    
    
    Next c

 これで、シート上は絞り込まれていますが、ListBox1にはシート上の全てのデータが表示されてしまいます。

 絞り込んだ結果のみを表示するにはどうしたらいいでしょうか

 Excel2010を使ってます。

 お教えください
----
 AutoFilterで絞り込んだリストをどこか空いている列に転記
 転記先をListBoxのListにして、転記先を削除、またはClear
 AutoFilterを解除

 では？
 (seiya)
----
 RowSOurceにセル領域を設定すると、Visibe じゃない領域も対象になる。
 コードでは、一応 SpecialCells で表示セルを取得してループさせているけど、
 結局は、RowSOurceにセル領域アドレスをセットしているので。

 たとえば以下のようなコードで試してみて。
 
    Dim ySh As Worksheet
    Dim Kensaku As Variant
    Dim c As Range
    Dim v As Variant

    Set ySh = ThisWorkbook.Worksheets("ToDoリスト")

    Kensaku = StrConv(UCase(Text1), vbNarrow)    'Text1(テキストボックス)に検索条件を入力

    With ListBox1
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "30;50;50;70;100;150"
    End With

     '範囲はA列〜F列、検索条件はB列の値を対象
    ySh.AutoFilterMode = False
    ySh.Range("A2").AutoFilter Field:=4, Criteria1:=Kensaku

    With Worksheets("作業シート")
        .Cells.ClearContents
        Intersect(ySh.AutoFilter.Range, ySh.AutoFilter.Range.Offset(1)).Copy .Range("A1")
        v = .Range("A1").CurrentRegion.Value
        ListBox1.List = v
    End With

 (ぶらっと)
----
 説明が足りなくてすみません。

 別の作業用シートに抜き出せばできたんですが、絞り込んだ結果がリストボックスに表示された後、そのいずれかの
 データを選択してデータの内容を更新したりデータを削除したりしたいので、作業用シートを使うとやりにくいかと
 思って「ToDoリスト」シート自体で操作しようとしたのですが、

 ぶらっと様のコードで試しましたが、入力済みのデータが下の方に移動して見出し行が消えてしまいました…
 (作業用シートを使わない場合）

 （toto)
----
 追記です。
 Listbox1は見出し行（「ToDoリスト」の2行目）をColumnHeads = Trueで表示させるようになっています。

 (toto)
----
 ColumnHeads = True　の場合、オートフィルター抽出データのみを相手にしたい場合、ToDoリストの現行リスト領域だけでの勝負はちょっと無理筋かな？
 seiyaさんからコメントあったように、同じシートのどこかに転記した上で、そこを使うか、あるいは作業シートで。 
 以下は、あくまで作業シートを使う案

 Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 6
        .ColumnWidths = "30;50;50;70;100;150"
    End With
 End Sub


 Private Sub CommandButton1_Click()
    Dim ySh As Worksheet
    Dim Kensaku As Variant
    Dim c As Range
    Dim v As Variant

    Set ySh = ThisWorkbook.Worksheets("ToDoリスト")

    Kensaku = StrConv(UCase(Text1), vbNarrow)    'Text1(テキストボックス)に検索条件を入力

     '範囲はA列〜F列、検索条件はB列の値を対象
    ySh.AutoFilterMode = False
    ySh.Range("A2").AutoFilter Field:=4, Criteria1:=Kensaku

    If ySh.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
        MsgBox "当該データはありません"
        Exit Sub
    End If

    With Worksheets("作業シート")
        .Cells.ClearContents
        ySh.AutoFilter.Range.Copy .Range("A1")
        With .Range("A1").CurrentRegion
            ListBox1.RowSource = .Resize(.Rows.Count - 1).Offset(1).Address(External:=True)
        End With
    End With

 End Sub

 (ぶらっと)
----
 ぶらっと様

 ありがとうございます。

 作業用シートに抜き出してリストボックスに表示するまではできたのですが、元のデータを更新・削除するために
 データの行番号を取得するのに難航しております…

 あれこれ試しましたがListBox1のListIndex番号を取得するだけしかできなくて…

 元データをユーザーフォームに表示するために「ToDoリスト」の行番号を取得するにはどうしたらいいでようか？

 (toto)
----
 > 元データをユーザーフォームに表示するために「ToDoリスト」の行番号を取得するにはどうしたらいいでようか？

 案として
 1) Filterする前に作業列に行番号を埋め込む。
 2) 作業列を含めてFilter
 3) 結果をComboBoxのListにする
 4) Click eventで
    Combobox2.List(ComboBox2.ListIndex, Ubound(ComboBox2.List,2))
    で行番号が取得できるはず。

 削除したら、一連の動作を繰り返し再度読み込む必要あり。

 (seiya)
----
 ありがとうございます。

 オートフィルタで絞り込んだ結果を操作するのは色々と制約があるのですね。

 ちょっと別の方法も視野に入れてもう一度考えてみます

 (toto)
----
 ＞オートフィルタで絞り込んだ結果を操作するのは色々と制約があるのですね。
制約というほどのものでもないけど、オートフィルタの動作自体は
絞込みから外れたレコード行を非表示にしているだけですから
抽出した結果からまたもとの位置を割り出そうとすると取得しにくい
ケースが出るかもしれませんね。
もとのリストの行番号が取得しにくい、ということはもとのデータに
ユニークなキーが欠如しているということ。
　
もとのデータに最初から連番などのユニークキーをつける方向も
検討してみてください。
（みやほりん）