VBA: Deleting Worksheet Consolidation References

[Jerry Sullivan]

Hi all,

We have some legacy workbooks that have numerous Consolidation references that need to be removed.

The references can be deleted manually using the Data tab > Data Tools group > Consolidate button

A web search shows some examples of how to Add Consolidation references using VBA, but I haven't been able to find how to Delete them using VBA.

The references can be listed by reading the Worksheet.ConsolidationSources property- but this is a Read-Only property.

TIA

 

[rlv01]

That's an interesting problem. Try this code. It seems to work for me after minimal testing. It focuses on just the activesheet; but of course could be extended to look at each sheet in a workbook.

Sub KillWorksheetConsolidation()
    Dim WS As Worksheet
    Dim I As Long
    Dim S As String
    Dim CSources As Variant

    Set WS = ActiveSheet

    With WS
        If Not IsEmpty(.ConsolidationSources) Then
            CSources = .ConsolidationSources

            For I = 1 To UBound(CSources)
                S = S & "#" & I & ":" & CSources(I) & vbCr
            Next I

            Select Case MsgBox("Range Consolidation found" & vbCr & vbCr & S & vbCr & "Delete?", vbYesNo Or vbQuestion, "Worksheet " & .Name)
            Case vbYes
                On Error Resume Next
                .Cells.Consolidate Sources:=Array(vbNullString), Function:=xlProduct, TopRow:=False, LeftColumn:=False, CreateLinks:=False
            End Select
        Else
            MsgBox "No consolidation references found", vbOKOnly Or vbInformation, "Worksheet " & .Name
        End If
    End With
End Sub

 

[Jerry Sullivan]

Hi rlv01,

Overwriting the existing consolidation sources with a blank array is a very nice approach. Thank you!