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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
772
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,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top