[[20190225001440]]　『VBA　#N/Aを含んでいる行を削除』（VBA初心者）　ページの最後に飛ぶ

『VBA　#N/Aを含んでいる行を削除』（VBA初心者）

VBAで、vlookupでエラーが出た、H列の#N/Aを含んでいる行を削除したいので、
下記のプログラミングを実行しましたが、型が一致しませんとエラーが出ます。
どこを修正すればよいかご教示ください。

Sub test()

    Dim i As Long
    Dim lastRow As Long

    lastRow = Workbooks("1.xlsx").Sheets("Sheet1").Cells(Rows.Count, 8).End(xlUp).Row

    For i = lastRow To 1 Step -1

        If InStr(Cells(i, "H"), "#N/A") Then          
        Cells(i, "H").EntireRow.Delete
        End If
    Next i

End Sub

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

 判定の部分に、cverr関数使ってみてはいかがでしょう？
https://www.tipsfound.com/vba/05cverr

 他に懸念点がありますので、一言二言

 ループ内のCellオブジェクトに、シート名の記述がないので、
 アクティブなシートで判定されると思います
 With Workbooks("1.xlsx").Sheets("Sheet1")
 で括るなど、工夫が必要そうです

 デリートですが、セルが削除させると再計算が行われます
 一度手動にしてから実行された方が処理速度が早くなると思います
 また、UnionメソッドなどでRangeオブジェクトをまとめて最後に削除
 など、いくつかやり方があると思うので調べてみてください
（稲葉） 2019/02/25(月) 06:04

 ちょっと表現間違えました
 Cellはオブジェクトじゃなくて、関数でした
 すみません
（稲葉） 2019/02/25(月) 07:14

 NAエラー判定の部分だけ。
 If WorksheetFunction.IsNA(Range("H" & i).Value) Then
 でどうでしょうか。
（ろっくん） 2019/02/25(月) 09:17

Textプロパティを明示する手も。

    If InStr(Cells(i, "H").Text, "#N/A") Then
（???） 2019/02/25(月) 11:18

出遅れた感がありますが、投稿しておきます。

型が一致しないと出るのは、「#N/A」が【文字列】ではなく、「エラー値」と呼ばれる型？であるからですね

やり方はいくつかありますが、まず文字列として扱いたいのであれば、CDtr関数で変換してしまうという手があります。ただし、「#N/A」の場合返ってくるのは「エラー 2042」という文字列になるので注意が必要です。（お使いのバージョンにより違いがあるかもです）

    Sub さんぷる1()
        Dim i As Long

        Stop '←ブレークポイントのかわり

        '▼対象のシートを明示
        With ActiveSheet

            '▼元案のとおり下から上にループ処理
            For i = .Cells(.Rows.Count, "H").End(xlUp).Row To 1 Step -1

                '▼対象セルの値が「#N/A」ならその行を削除
                If CStr(.Cells(i, "H").Value) = "エラー 2042" Then
                    .Cells(i, "H").EntireRow.Delete
                End If
            Next
        End With

    End Sub

これを踏まえて、エラー値をわざわざ文字列として扱う必要が無く、#N/A 以外のエラーもひっくるめて対象としてよいのであれば、こんな感じでOKです。

    Sub さんぷる2()
        Dim i As Long

        Stop '←ブレークポイントのかわり

        '▼対象のシートを明示
        With Workbooks("1.xlsx").Sheets("Sheet1")

            '▼元案のとおり下から上にループ処理
            For i = .Cells(.Rows.Count, "H").End(xlUp).Row To 1 Step -1

                '▼対象セルの値がエラー値ならその行を削除
                If IsError(.Cells(i, "H").Value) Then
                    .Cells(i, "H").EntireRow.Delete
                End If
            Next
        End With

    End Sub

そうでなくて、あくまでエラー値のうち「#N/A」だけ対象にしたいということであれば、もう一手間加えてこんな感じではどうでしょう？

    Sub さんぷる3()
        Dim i As Long

        Stop '←ブレークポイントのかわり

        '▼対象のシートを明示
        With Workbooks("1.xlsx").Sheets("Sheet1")

            '▼元案のとおり下から上にループ処理
            For i = .Cells(.Rows.Count, "H").End(xlUp).Row To 1 Step -1

                '▼対象セルの値がエラー値であるかをまず判定
                If IsError(.Cells(i, "H").Value) Then

                    '▼さらに「#N/A」であるかを判定して、真なら行を削除
                    If .Cells(i, "H").Value = CVErr(xlErrNA) Then
                        .Cells(i, "H").EntireRow.Delete
                    End If
                End If
            Next
        End With

    End Sub

【参考】
https://qiita.com/nukie_53/items/6c2d9d4699149bebe863
https://www.tipsfound.com/vba/05cverr

私にはなかった発想ですが、???さんのように、textプロバティを使うというのもありですね。

（もこな２） 2019/02/25(月) 11:47

皆様ありがとうございます。
無事できました。
（VBA初心者） 2019/02/25(月) 22:39

コメント返信：

[ 一覧(最新更新順) ]

[[20190225001440]] 『VBA #N/Aを含んでいる行を削除』（VBA初心者） ページの最後に飛ぶ

[[20190225001440]]　『VBA　#N/Aを含んでいる行を削除』（VBA初心者）　ページの最後に飛ぶ