質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.52%

  • Visual Studio

    1831questions

    Microsoft Visual StudioはMicrosoftによる統合開発環境(IDE)です。多種多様なプログラミング言語に対応しています。

  • Excel

    1534questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

VBAで複数あるシートからセルに#REFがあるシートを削除すり方法

解決済

回答 4

投稿

  • 評価
  • クリップ 0
  • VIEW 176

uho1234

score 3

VBAで特定のシート(7シート目)以降からセルに#REFのあるシートを見つけてそのシートを削除する方法がわかりません。

特定のシート名やセルならばis error関数を使えばできることは分かったのですがブック全体から探し出す方法はありますでしょうか?

7シート以降のシート数やシート名は固定ではありません。

どなたか教えて頂ければ幸いです。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

+1

パターンを分けて検索してみたらいかがでしょうか?

 任意のブックに対し、巡回処理を行う

1:- 任意のブックに対し、全シートを巡回する手順を調べる。
全てのシートをループして処理する方法
2:特定のシートから処理を行う方法を調べる。→各シートのインデックスを調べる。
シートのインデックス番号を取得する(Indexプロパティ)

今回は7シート目からという条件があるので、Foreach ではなく、Do while ~ Loopを使って、インデックスの初期値を7にしておくといいでしょう。

引き続き…

 アクティブシート内の処理を行う。

3: 走査範囲を取得
4:判定文を作成
5:任意のシートを削除する。

3から5は大丈夫のようなので、1と2のアドバイスをするとこんな感じです。
これを応用(走査範囲が部分的じゃないので、ループ処理となります)すれば、いけると思います。

Dim s As String
Dim ws
Dim i, wcnt, idx As Integer
Application.DisplayAlerts = False
wcnt = Worksheets.Count 'ワークシート数
i = 7 '処理を行うシートのインデックス'
Do While i <= wcnt
   Set ws = Sheets(i) 'インデックス番号からシートオブジェクト取得
   ws.Activate '任意のシートをアクティブにする
   If 判定式 Then
        ActiveSheet.Delete
        wcnt = wcnt - 1 '削除した場合は上限ワークシート数を減らしておくこと
   End If
    i = i + 1
Loop
Application.DisplayAlerts = True
End Sub

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/23 12:01

    御回答ありがとうございます。

    ごめんなさい書き方が悪かったです。
    3〜5についてですが
    特定のシート目からREFを探し出す方法はわかったのですがそれを指定して削除する方法は分かりませんでした。

    キャンセル

  • 2018/07/23 12:05 編集

    それだとサンプルに書いている通り、任意のインデックスをアクティブにして、それを削除すればいいですよ。

    Set ws = Sheets(i) 'インデックス番号からシートオブジェクト取得
    ws.Activate '任意のシートをアクティブにする
       If 判定式 Then
    ActiveSheet.Delete
    wcnt = wcnt - 1 '削除した場合は上限ワークシート数を減らしておくこと
       End If

    このあたりが、その処理になります。

    キャンセル

+1

それだとサンプルに書いている通り、任意のインデックスをアクティブにして、それを削除すればいいですよ。

Set ws = Sheets(i) 'インデックス番号からシートオブジェクト取得
ws.Activate '任意のシートをアクティブにする
If 判定式 Then
ActiveSheet.Delete  'アクティブシートの削除
wcnt = wcnt - 1 '削除した場合は上限ワークシート数を減らしておくこと

このあたりが、その処理になります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/23 12:13

    何度もありがとうございます!
    やってみます

    キャンセル

  • 2018/07/23 17:37

    色々試したのですがなかなか思うようにいきません。

    このように書いてみたのですが、#REFに関わらずにシートが削除されてしまいます
    加えて一度で全部削除されずに何度か実行する毎に削除されます
    理由が分からず苦戦しています。アドバイスを頂けないでしょうか。

    Sub aa()
    Dim s As String
    Dim ws
    Dim i, wcnt, idx As Integer
    Application.DisplayAlerts = False
    wcnt = Worksheets.Count 'ワークシート数
    i = 7 '処理を行うシートのインデックス'

    Do While i <= wcnt
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(18).Cells.SpecialCells(xlCellTypeFormulas, xlErrors) 'インデックス番号からシートオブジェクト取得
    ws.Activate '任意のシートをアクティブにする
    If Not res Is Nothing Then
    ActiveSheet.Delete
    wcnt = wcnt - 1 '削除した場合は上限ワークシート数を減らしておくこと
    End If
    i = i + 1
    Loop
    Application.DisplayAlerts = True
    End Sub

    キャンセル

  • 2018/07/23 17:51

    変数wsにはワークシートオブジェクトだけ入れてください。これだとセル範囲まで入れてしまっています。

    それから今回は特定のセルだけを見て判断する
    のではなく、各シート内の任意のセル範囲の中から#REFという情報を持ったセルを探索するので、
    任意のセル内を走査する方法を探してみてください。そして、セル内で、任意の情報を持った
    セルが見つかったら、削除するようにします。

    このサイトなら参考になるかな?
    http://officetanaka.net/excel/vba/cell/cell10.htm

    キャンセル

  • 2018/07/23 18:03

    あと、otnさんのいうとおり、すぐに削除するとインデックス値が狂うおそれが高いので、一旦退避させておいて、あとで任意の番号に対し、一挙にシートを削除する手段を使った方が的確だと思います。

    キャンセル

  • 2018/07/24 08:47

    なるほど!ごっちゃになっていました!
    何度もありがとうございました!
    本当に助かりました!

    キャンセル

+1

今回ぐらいの処理ならDo...LoopよりFor文の方が好みなのでFor文版を。

containsRefError関数は中身が入っていないので適宜埋めてください。

Sub Sample()

    ' 処理対象のブック
    Dim targetBook As Excel.Workbook
    Set targetBook = ThisWorkbook 'マクロの書かれているブック
    'Set targetBook = ActiveWindow '最前面にあるブック
    'Set targetBook = Workbooks.Item("ブックの名前") '特定の名前のブック


    'シート一覧
        '質問内容からワークシートのみと断定できなかったため、Sheetsプロパティを使用
    Dim tgtSheets As Excel.Sheets
    Set tgtSheets = targetBook.Sheets

    Excel.Application.DisplayAlerts = False

    '何シート目以降から処理をしたいか
    Const SheetIndexLBound = 7
    '末尾のシートから、SheetIndexLBound番目のシートまでループ
    Dim sheetIndex As Long
    For sheetIndex = tgtSheets.Count To SheetIndexLBound Step -1

        If TypeOf tgtSheets.Item(sheetIndex) Is Worksheet Then
            Dim ws As Excel.Worksheet
            Set ws = targetBook.Sheets.Item(sheetIndex)
        Else
            '現在のシートがワークシート以外なら次へ
            GoTo Continue
        End If

        'シートのセルに、REFのエラーが含まれていたら
        If containsRefError(ws.UsedRange) Then
            ws.Delete
        End If

Continue:
    Next sheetIndex

    Excel.Application.DisplayAlerts = True

End Sub

Function containsRefError(targetRng As Excel.Range) As Boolean
    Dim refErr As Variant
    refErr = CVErr(XlCVError.xlErrRef)

    'TODO:targetRngにREFエラーが含まれているかどうか探す処理

    '含まれている場合は
    containsRefError = True

    '含まれていない場合は
    containsRefError = False

    'とする
End Function

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/24 08:45

    ありがとうございました!
    本当に助かりました!

    キャンセル

0

特定シートを対象とした方法が分かっているのなら、対象シートそれぞれについて、繰り返せば良いだけでは?
どこに困難があるのか分かりません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/07/23 11:49

    書き方が悪かったです
    特定のシート名を書いてセルのエラーを検索するのは分かりましたがそれを指定して削除する方法が分かりません。

    シート名は変わることもあるためシート名を書いて探し出す方法は使えません。

    キャンセル

  • 2018/07/23 12:20

    シートは、~.Sheets(番号)で、参照できます。
    削除すると番号が変わるので、最初に全部のシートオブジェクトを配列に入れるのが良いでしょうね。

    キャンセル

  • 2018/07/23 17:38

    番号でも参照できるのですね。 知りませんでした
    参考になります ありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.52%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 解決済

    VBA google画像検索 画像表示

    エクセルのVBAで文字列に対する画像が表示されるマクロを作成したいです。 やりたいこととして例えば、 A列に5つワードが書かれており、 そのワードをひとつずつgoogleで画像検

  • 解決済

    エクセル VBA 特定セルを一つのシートにまとめたい

    ◆印刷範囲外に、書かれた赤と青で囲まれた数値をまずそのまま でも良いので別シートに表示させたいです。 (全11シートあり全て同じセルに入っています) ◆そして、その表示した数値の最

  • 解決済

    エクセル 関数とVBAを使ったシフト表

    エクセルにて、シフト表を作成しています。 関数とVBAを使用して作成しています。 ファイルの容量は2400KB程度でしょうか、起動するにも少々重いです。 作成した時点では不具

  • 解決済

    表を結合させたい VBA(EXCEL2013)

    前提・実現したいこと VBA超初心者です。 実現したいこと:別シート(日別)で同一箇所にある「日別」の表をループで結合させたい。 プログラミングに分かりやすく説明付

  • 解決済

    既存ブックへのシートコピー

    保存対象のシートを別ブックにコピーして保存するようなマクロを組んだのですが、既存ブックが初期状態(sheet1)のみだった場合、sheet1に対してシートコピーを行い、それ以降は右

  • 解決済

    VBA セルの初期状態を保持して置いて、処理後にその情報をもとにセルデータを元に戻したいです。

    現在VBAにて下記のような処理を考えています。 前提 Excelの数式タブ>名前の管理にて、 Range("A1:D3")を"InitDataRange"と命名していることとし

  • 受付中

    VBA データの検索から貼り付け

    いつもご教授頂きありがとうございます。 初歩的な質問ですが、本やインターネットで調べても検討がつかないので、 ご教授頂けると助かります。 データA A列:商品名 B列:重量

  • 解決済

    VBA 実行時エラー'1004' '.xls'にアクセスできません

     前提・実現したいこと VBAで開いていないExcelの特定のシート名のデータをコピーし、別のブックに貼り付けようとしています。  発生している問題・エラーメッセージ 実行時エ

同じタグがついた質問を見る

  • Visual Studio

    1831questions

    Microsoft Visual StudioはMicrosoftによる統合開発環境(IDE)です。多種多様なプログラミング言語に対応しています。

  • Excel

    1534questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。