Excel2013VBAでFindで検索できなかったセルに入力した番号の行範囲の行表示非表示について

Question

いつもお世話になってます。
またわからないのでご教授下さい。

添付画像のようにBに№1から№6までの番号を振っています。
例えば、№1であれば8行目から15行目が№1の行範囲です。構成は、№と記入したセルの上に空白行があり、その下に№と記入したセルがありその下の行に品名があり、その下が結合セルになっており、この結合セルの行数は挿入したり削除したりと変動します。結合セルも下にもう1行あります。これで一つの№の行の塊になります。行数は変数jで取得しています。

問題は、№の行の塊が丸ごと非表示になっている場合があることです。複数非表示の可能性もあります。
添付画像では№2が非表示になっています。

やりたいことは、インプットボックスに№の数字を入れると、その№の行が表示されていればその№の行の塊で非表示にして、非表示ならば、再表示したいと思っています。
Findで検索して検索できた場合は、№の行は表示されているはずなので普通に非表示にできました。
問題は、非表示の場合どこに表示したい№が書いてあるか見つけられないことです。
画像では、№2の位置が見つからないので下のコードの　Else　の部分に非表示だった場合の処理を記述する必要がありますが、いい方法が思いつきませんでした。
何か、良い方法があるでしょうか？
よろしくお願いいたします。


Sub sample()
  Dim buf As Long
  Dim p As Long
  Dim j As Long
  Dim Titles As Variant
  Dim v As Variant
  Dim bufRng As Range
  Dim TargetCell As Range

    buf = InputBox("№の数字を入力してください")

 For p = 1 To 6
   Titles = Array("№" & p)

    For Each v In Titles 'vに№1、№2、№3・・・と入れていく
      Set bufRng = Columns(2).Find(What:=v, LookIn:=xlValues) '2(Ｂ)列目から№を検索
      If Not bufRng Is Nothing Then
　　　Set TargetCell = bufRng
        If TargetCell = "№" & buf Then
　'jに結合の行数を数えて(№の上と№と名前の行と下1行分の合計4行)の結合していない行もプラス
            j = TargetCell.Offset(2, 0).MergeArea.Rows.Count + 4 
   '№*の範囲全体を非表示してやる
            TargetCell.Offset(-1, 0).Resize(j, 1).EntireRow.Hidden = True
        End If

      Else
     '検索できなかった№は非表示になっていると考えられるのでその場所を特定してその範囲を再表示する
     ?????.Offset(-1, 0).Resize(j, 1).EntireRow.Hidden = False
　　End If
    
　Next v
Next p

End Sub

WindFaller · Accepted Answer

こんにちは。

これでうまくいくかは分かりませんが、要するに、最初にRangeの範囲を非表示・表示に関わりなく、その構造をCollection変数に登録しておいて、そこから導き出す方法を、最初、配列にしようかと思いましたが、なんとくなくそちらのほうがよいかなと考えました。非常にたくさん数があるのでしたら、その範囲の登録は別にすると良いと思います。あまり格好が良くありませんが、試してみてください。ダメなら、また別な方法を考えます。表示・非表示はトグルにしています。

Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)

列の範囲の決め方は、全体が見えていませんので、これで良いのかは、疑問が残ります。

'//
Sub TestMacro()
　Dim rng As Range
　Dim sRng As Range
　Dim colRng As Collection
　Dim r As Variant
　Dim i As Variant
　Dim bln As Boolean
　Set colRng = New Collection　　'★
　Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
　Set sRng = rng.SpecialCells(xlCellTypeConstants)
　For Each r In sRng
　　If r.Value Like "№*" Or r.Value Like "その他*" Then
　　　colRng.Add r
　　End If
　Next r
　i = InputBox("№の数字を入力してください")
　If StrPtr(i) = 0 Then Exit Sub
　i = CLng(i)
　If colRng.Count > i Then
　　bln = Not (colRng(i).EntireRow.Hidden)
　　Range(colRng(i).Offset(-1), colRng(i + 1).Offset(-1)).EntireRow.Hidden = bln
　ElseIf colRng.Count = i Then
　　'最後尾のセル群です。サイズは、3行にしました。
　　bln = Not colRng(i).EntireRow.Hidden
　　colRng(i).Offset(-1).Resize(3).EntireRow.Hidden = bln
　End If
End Sub

ママチャリ · Answer

再度の補足、ありがとうございます。ただ、やはり意味がわかりませんでした。
本当の課題は、「プログラミングの方法」ではなく、「ユーザの操作方法をどうするか」なのでしょうか？
であれば、質問のしかたを変えないと有効な回答は得られないと思いますよ。回答者側では、細かい事情が分からないですから…。

ママチャリ · Answer

すいません。補足コメントの内容が理解できませんでした。サンプルでは、No.６までしかないが、増える可能性があるということでしょうか？
そもそも私には、No.1~No.6までループさせている意味が分かりませんでした。入力されたNo.を検索して、表示・非表示を切り替えるだけなら、次のようなコードで十分だと思うのですが…。

Sub sample()
    Dim buf As Long
    Dim j As Long
    Dim v As Variant
    Dim TargetCell As Range
    buf = InputBox("№の数字を入力してください")
    v = "№" & buf
    Set TargetCell = Columns(2).Find(What:=v, LookIn:=xlFormulas)
    If Not TargetCell Is Nothing Then
        j = TargetCell.Offset(2, 0).MergeArea.Rows.Count + 4
        TargetCell.Offset(-1, 0).Resize(j, 1).EntireRow.Hidden = _
            TargetCell.EntireRow.Hidden Xor True
    End If
End Sub

ママチャリ · Answer

「見つからない」ということは「位置を特定できない」ということなので、何らかの方法で「見つける」必要があります。
一番手っ取り早いのは、FindメソッドのLookInパラメータをxlFormulasにすることにより、非表示の行でも見つけてくれます（たぶん）。
見つかった行のHiddenプロパティを判断して、表示、非表示を切り替えれば、いけるような気がします。

Excel2013VBAでFindで検索できなかったセルに入力した番号の行範囲の行表示非表示について

こんにちは。

再度の補足、ありがとうございます。

すいません。

「見つからない」ということは「位置を特定できない」ということなので、何らかの方法で「見つける」必要があります。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング