VBA: Deleting Worksheet Consolidation References

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
721
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.

Code:
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi rlv01,

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

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,271
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top