VLOOKUP関数でエラーの表示された行だけを削除するにはどうすればい

Question

VLOOKUP関数でエラーの表示された行だけを削除するにはどうすればいいでしょうか。

http://oshiete.goo.ne.jp/qa/5920035.html
の質問にて作成したワークシートCがあります。

ワークシートCのA列はドラッグして日付を埋めたので、昇順で365日日付が並んでいます。（2005年1月1日～現在まで）

B列～K列までは、ワークシートA（為替）、ワークシートB（株式）からVLOOKUPで引っ張ってきた数値が入っています。

ドラッグして日付を埋めたので、A列にはマーケットが開いてない土日も日付が入っていますが、その結果、土日の日付を参照した行には#N/Aというエラーが表示されている状態です。
（Ex,　A3は2005/01/02という日付が入っていて、B3～K3は#N/Aというエラーが表示されている状態です。）

この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

#N/Aを判別するようなものにすればいいんだと思いましたが、その具体方法が分かりません。

マクロも初心者なので、出来るだけ具体的に教えて頂けると助かります。

宜しくお願いします。

keithin · Accepted Answer

＞この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

先のご相談では，片方のマーケットしか開いていない日もあるというお話でしたね。
その前提に立つと単純に土日だけでなく，為替列でエラーになっている行とか，株式列だけがエラーになっている行などもあるはずです。

つまりやるべきは「#N/Aが表示されている行を削除する」ではなく，「B列とG列が共に#N/Aになっている行」を抽出して削除ということになります。

＃以下のご説明は，マーケットの無い日がエラーでも""でも同じです。

手順：
オートフィルタを取り付ける
B列でエラー値(若しくは空白）で絞る
G列でエラー値(若しくは空白）で絞る
共にエラー値(若しくは空白）である行だけが残るので，選んで右クリックして削除で行単位で上に詰めて完成。

keithin · Answer

ん？　まだ解決してないって事は，コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか？

その前に，そもそも前のご質問でエラーの解消は出来たんでしょうか。
それとも，前の回答の式は破棄してご自分の式で行くことにしたので，まだエラーが出たままなのでしょうか。その場合はもう前のご相談とは話が違うのですから，「今こういう式にしています」ときちんと情報提供してください。そういった「今こうなっている」が不明のままマクロだけ人に書かせても，的外れなままです。

エラーの消去も，こちらのご質問で先に回答しておいたように，Ｂ列を消して終わりでは間違った対処です。

間違ったご質問：
＞（Ex,　A3は2005/01/02という日付が入っていて、B3～K3は#N/Aというエラーが表示されている状態です。）

＞この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

それに誘導された間違った回答：
Sub macro1
range("A:K").autofilter field:=2, criteria1:="#N/A"
activesheet.autofilter.range.offset(1).delete shift:=xlshiftup
activesheet.autofiltermode = false
end sub

Ctrl+G（ジャンプのセル選択）でエラーセルを拾うのも，同様に間違った対処です。

それと先のご相談時点と，為替・株からコピーしてきた列数が違っていますが，今結局どうなってるんでしょうか。

もしかしたらこうかな？の憶測で書いたマクロ１：
sub macro2()
range("A:K").autofilter field:=2, criteria1:="#N/A"
range("A:K").autofilter field:=7, criteria1:="#N/A"
activesheet.autofilter.range.offset(1).delete shift:=xlshiftup
activesheet.autofiltermode = false
end sub

もしかしたらこうかな？のマクロ２：
sub macro3()
range("A:K").autofilter field:=2, criteria1:="="
range("A:K").autofilter field:=7, criteria1:="="
activesheet.autofilter.range.offset(1).delete shift:=xlshiftup
activesheet.autofiltermode = false
end sub

imogasi · Answer

質問の内容をやや一般化して、「そもそも論」をやってみます。回りくどいと思うと思いますが少しお付き合いください。Ｅｘｃｅｌで、ある行を見せないようにするには（１）そのデータ行はある（残す）が非標示にする（２）そのデータをなくした表を新たに別のシート（た同一シートの別余白列に）に作るの２つがあります。（１）は　（Ａ）自己目視選択　（Ｂ）条件指定で自動での２つがあります。全体が少ない行で、非表示にする場合でエラーも少なく、再々の作業でなければ（Ａ）でも済ませられます。しかし「該当行が多いとか再々の作業がある場合にやる場合は（Ｂ）を望むでしょう。（Ａ）はＣＴＲＬキーを押しつつ該当行を目視で選択してクリックして、書式ー行ー非表示です。（Ｂ）は非表示はデーターフィルタオプションの設定しかありません。選択して編集ー削除もありますが。例えばセルの値が　2　に等しく無いものを抜き出すには<>"2"を条件にします。ーー（２）は　（Ｘ）関数の抜き出し問題として考えるか　（Ｙ）ＶＢＡで抜き出すかになるでしょう。注意が必要な点はＶＬＯＯＫＵＰ関数のエラーで、見つからない行のエラーのセルの値は、見た目「#N/A」です. しかし文字列の「#N/A」ではなくフィルタオプションの設定の条件は　<>#N/A でフィルタします。下記例でＥ１：Ｅ２値 <>#N/A です（Ｘ）の関数で抜き出す方法は、Ｇｏｏｇｌｅで「imogasi方式」照会すると沢山回答例が出ますが略。その場合エラーで無い行の余白列にサインの１なりを改めてつけておくと考えやすい。ーー（Ｙ）のＶＢＡではエラーのセルの判別に知識が要ります。データ例がＡ１：Ｂ４でコード値 1a 2#N/A 3b として検索表はＩ１：Ｊ２に 1a 3b とします。上記例で、例えばＢ２の式は =VLOOKUP(A2,$I$1:$J$2,2,FALSE)で出してます。ーＶＢＡコードは、一例（方法）として Sub test01() j = 2 For i = 2 To 4 If IsError(Cells(i, "B")) = True Then Select Case Cells(i, "B") Case CVErr(xlErrNA) Case Else Cells(j, "G") = Cells(i, "A") Cells(j, "H") = Cells(i, "B") End Select Else Cells(j, "G") = Cells(i, "A") Cells(j, "H") = Cells(i, "B") j = j + 1 End If Next i End Sub のような判定になります。ーほかにフィルタオプションの設定の操作そのものをＶＢＡで行う方法もあります。Ｇｏｏｇｌｅででも「ＶＢＡ　フィルタオプションの設定」げ照会してください。またフィルタオプションの設定で別シートに結果を出すにはコツが必要ですがＧｏｏｇｌｅででも照会してください。ＶＢＡで別シートに結果を出すのはSheet2.Cells(j,"A")=Sheet1.Cells(i,"A")のような書き方になりますが詳細は略。参考URL：http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1022022490

KURUMITO · Answer

#N/Aの表示を出さない工夫が必要でしょう。
VLOOKUP関数を使用する場合には次のような式を使っておられることでしょう。
ワークシートＣのＡ列に日付があるとしてＢ2セルには例えば
=VLOOKUP(A2,為替!$A:$C,3,FALSE)
この式では為替シートのＡ列にその日付が無ければエラーとなります。
それを解消するためには一般には次のような式にして使用します。

=IF(OR(A2="",COUNTIF(為替!$A:$A,A2)=0),"",VLOOKUP(A2,為替!$A:$C,3,FALSE))

このように式を変形して使うことでエラー表示を解消することができますね。

どうしてエラー表示のある行を解消したいのでしたら次のようにします。
エクセル2007でしたら「ホーム」タブの「検索と選択」から「条件を選択してジャンプ」をクリックします。また、エクセル2002などでしたら「編集」メニューから「ジャンプ」を選択し、表示の画面で「セル選択」をクリックします。
表示される画面で「数式」にチェックをしたうえで「エラー値」のみにレ点が有る状態にし「ＯＫ」します。
エラー表示のあるセルがすべて選択状態になりますのでアクティブ状態にあるセルを右クリックして「削除」をクリックします。表示の画面で「行全体」にチェックをしてＯＫします。
これでエラー表示のある行をすべて削除することができます。

VLOOKUP関数でエラーの表示された行だけを削除するにはどうすればい

＞この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

ん？ まだ解決してないって事は，コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか？

質問の内容をやや一般化して、「そもそも論」をやってみます。

#N/Aの表示を出さない工夫が必要でしょう。

この回答への補足

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

このQ&Aを見た人はこんなQ&Aも見ています

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

このQ&Aを見た人がよく見るQ&A

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

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

ん？　まだ解決してないって事は，コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか？