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

Question

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

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

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

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

Answer 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

Comments

[uho1234]

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

Answer 2

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

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

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

Comments

[uho1234]

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

[uho1234]

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

このように書いてみたのですが、#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

[FKM]

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

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

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

[FKM]

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

[uho1234]

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

Answer 3

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

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

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

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

引き続き…

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

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

Comments

[uho1234]

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

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

[FKM]

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

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

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

Answer 4

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

Comments

[uho1234]

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

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

[otn]

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

[uho1234]

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