エクセルでB列に＃N/Aが入った行を非表示にしたい

質問

添付表のように2枚のシートからの検索結果一覧表を作成したのですが、検索対象によっては各シートで100件程度の場合があり、Ｂ列が＃N/Aの行を非表示にしたいのですが。

また、Book保存時には非表示を解除するところまでコード化していただけるとありがたいです。
次回検索した人がピンクの列の一部が非表示になっていることに気付かない可能性がありますので。

回答No.3

kkkkkm · ベストアンサー

フィルターを利用すればいかがですか
4行目以降でB列の最終行までを対象とします。

Sub Test()
Dim LastRow As Long

With Sheets("Sheet1")
If .AutoFilterMode <> True Then
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range(.Cells(4, "A"), .Cells(LastRow, "B")).AutoFilter
End If
LastRow = .AutoFilter.Range(.AutoFilter.Range.Count).Row
.Range(.Cells(4, "A"), .Cells(LastRow, "B")).AutoFilter Field:=2, Criteria1:="<>#N/A"
End With
End Sub

終了時(どちらか選択してください)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'フィルターモードはオンのまますべて表示する
If Sheets("Sheet1").FilterMode = True Then
ActiveSheet.ShowAllData
End If

'フィルターモードをオフにする
Sheets("Sheet1").AutoFilterMode = False

End Sub

お礼

早々のご回答ありがとうございました。

なるほど！！
フィルタで行けますね！
同じような事を「マクロの記録」で使っているBookもあります。
＃N/Aというような特殊な文字なのでウッカリしました。

ご回答で一発解決出来そうです。
早速試してみます。

回答No.4

kkkkkm

No.3の追加です。

毎回行数が変動するのでしたら
終了時には
'フィルターモードをオフにする
Sheets("Sheet1").AutoFilterMode = False
にしたほうがいいと思います。

お礼

ご回答ありがとうございます。
試してみて分かったのですが、ご回答のコードを実行するとちゃんと必要な行のみ（＃N/A行抜きで）表示されることに気付きました。

つまりB2に検索文字入力後、ご回答のマクロを登録した「表示」ボタンで検索文字に合わせて表示が更新されることに気付きました。
よって「表示」ボタンをクリックするようにすればクローズ時のクリアは不要となりました。

実は続けて検索することも多いので、表の更新（クリア）はクローズ時では問題があり、B2セルの変更VBAに変えようと思いながら試行しているうちに上記で解決出来ることに気付きました。

ちなみに検索はB列を
=VLOOKUP($B$2&$A5,部署ﾘｽﾄ!$B:$M,4,0)
で2つの部署別シートから検索するようにしてありますのでB2に検索文字を入力するたびに表示が更新され、余分な行に＃N/Aが表示されます。
検索文字によっては最大100件と見込んで式を100行に入力してあります。
よって該当なしの場合式の入った100行が＃N/Aとなり、シート②の検索結果がそのあとに続くことになってしまうのでHELPお願いした次第です。

お手数をお掛けしましたが、おかげで何とかなりました。

回答No.2

imogasi

#1です。
下記のようなことで、目的について、良いのかな？？
ーー
下記例でやって見ました。
Ａ列に
a
s
d
d
f
g
#N/A
#DIV/0!
s
#DIV/0!
#NAME?
w
e
のデータを作った。#DIV/0！などはエクセル関数で出るエラーの式とデータで作ったもの。
そういう、文字列の入力ではない。
ーーー
標準モジュールに
Sub オートフィルタで除外を設定()
ActiveSheet.Range("A1").CurrentRegion.AutoFilter _
Field:=1, Criteria1:="<>#DIV/0!", Operator:=xlAnd, Criteria2:="<>#N/A"
End Sub
を作り、実行した。
#DIV/0!、か、または#N/Ａが出ている行を非表示にできた。
３番目に#NAME？の行も非表示に加えようとしたが、条件数の制約が２までらしく、実現せず。
https://excel-ubara.com/excelvba4/EXCEL259.html
複雑な条件（複数除外等）のオートフィルター（AutoFilter）
で「２条件までのこと」を述べているようだ。
ーー
AutoFilter がコード的に簡単なので、やって見たが、他の方法では、やれてない。
非表示にしただけなので、データの「すべて表示」の操作をすれば、シート上で、当初のデータの見てくれに戻る。

お礼

ご回答ありがとうございました。

大変参考になりました。

回答No.1

imogasi

＞コード化していただけると
VBAコードをつくれということか。丸投げだな。
操作でやることを考えてはどうか。他人にやらすなら、その操作を文章で、シート（の初めの方に）に掲げて置いたらどうか？
＞検索対象によっては各シートで100件程度
ということは色んな人が、いろんなテーマで検索するのに耐えるようなコードを望むのかな。

お礼

ご回答ありがとうございました。

ハイ。
＞色んな人が、いろんなテーマで検索するのに耐えるようなコードを望 みます。