如何在Excel中查找单元格的所有依赖项(也包括条件格式和数据验证)

I need to cleanup a very complex Excel file.

我需要清理一个非常复杂的Excel文件。

To be able to erase a cell, I need to make sure it's not used somewhere in the workbook. I know it is possible to find out if a cell is used in another cell's formula by going in Formula auditing > Trace dependents but this does not work if the cell is used in the following contexts :

为了能够删除单元格，我需要确保在工作簿中没有使用它。我知道有可能发现一个细胞是否在另一个细胞的公式中使用了公式审计>跟踪依赖项，但如果在以下情况下使用该单元，这就不起作用了:

Part of a range used for a drop-down list in the Data validation of another cell
在另一个单元格的数据验证中用于下拉列表的范围的一部分
In a formula part of the Conditional formatting of another cell.
在另一个单元格的条件格式的公式部分中。

These 2 Excel features are used a lot in the workbook.

这两个Excel功能在工作簿中经常使用。

Do you know a way to found out those dependencies ?

你知道找出这些依赖关系的方法吗?

1 个解决方案

#1

Try something like this, not 100%, but looks like it works from simple tests

试试这样的东西，不是100%的，但是看起来它可以从简单的测试中工作

Sub DependantTest()

For Each i In DependantOnValidation(Range("A1"))

    Debug.Print i

Next i

End Sub

Using this function

使用这个函数

Function DependantOnValidation(rngLookAt As Excel.Range) As Collection

Dim ws As Worksheet Dim rInspect As Range Dim rWorking As Range Dim rIntersect As Range

Set ws = ThisWorkbook.Worksheets("sheet1")

On Error Resume Next

Set DependantOnValidation = New Collection

For Each rInspect In ws.Range("a1:z1")

    Set rWorking = Range(Replace(rInspect.Validation.Formula1, "=", vbNullString))

    If Not rWorking Is Nothing Then

        Set rIntersect = Application.Intersect(rngLookAt, rWorking)
        DependantOnValidation.Add rInspect.Address

    End If

    Set rWorking = Nothing
     Next rInspect

End Function

and for CF something like this, not complete tho

对于CF来说，不是完全的tho

If rInspect.FormatConditions.Count > 0 Then
    For Each fCondition In rInspect.FormatConditions
        If fCondition.Formula1 <> "" Then
            If InStr(1, fCondition.Formula1, rngLookAt.Address(True, True)) > 0 Or _
                    InStr(1, fCondition.Formula1, rngLookAt.Address(False, True)) > 0 Or _
                        InStr(1, fCondition.Formula1, rngLookAt.Address(True, False)) > 0 Then

            End If
        End If
    Next fCondition
End If

#1