Hi,
I cannot delete a column due to the error message “You cannot insert or delete cells when the affected range intersects with pivot table”.
But no pivot table is visible at all in this column. One probably exist as I tried to insert one, but with no success.
I’ve tried to go through the Locator, which lists many objects (images, Drawings, …), but not pivot tables.
This is really frustrating.
Googling a bit (even here) give no answer.
Do anyone has a practice to handle that ? ( Even with Basic code )
Thank you
Moving the question from Français category to English.
Select all (Ctrl+A), Delete, then go to the bottom right (Ctrl+End): the pivot table is possibly there. Take note of the reference, Undo (Ctrl+Z), and go back there (the reference) to see what is there.
The pivot table also could be in some hidden row/s.
Hi JR729,
thank you for your help, but my Pivot Table was not there.
I finally wrote a small macro (language is ‘basic’) to find any (even invisible) pivot table.
So I found mine and could delete it.
I share it below.
To use it, just call the LO macro editor (ALT-F11), possibly create a module if none already exist, and paste the following code into it. Then select any part of the pasted code, and run it (F5).
It will display a message box with every found (even hidden) Pivot table contained in the current calc document.
Hope this will help
' =========================================
Sub DisplayPilotTables
' =========================================
' Scans sheets and display address of every (even invisible) pivot table
dim oSheet as object, oPilot as object
dim msg As string, SrcAddr As String, OutAddr As String
Dim oConv As object
oConv = ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion") ' Creates a cellRangeAddress to string address convertor
for each oSheet in thiscomponent.sheets ' Iterate sheets in document
If msg<>"" Then msg=msg & CHR(10) ' First Info message does'nt need a prepending \n
msg=msg & "Look in sheet [" & oSheet.Name & "]:" & CHR(10) ' Start building a new instance of information to display for this sheet
SrcAddr="" ' reset Address of datasource Pilot table (to check later if we find any)
for each oPilot in oSheet.DataPilotTables ' Iterate Pivot Tables in current sheet
oConv.referenceSheet = oSheet.RangeAddress.Sheet ' Set referenceSheet in convertor so it does not include sheetname in string address if it is the same as current sheet
oConv.Address = oPilot.OutputRange ' Convert Pilot table OutputRange 'cellRangeAddress' to a string address
OutAddr = oConv.UserInterfaceRepresentation
oConv.Address = oPilot.SourceRange ' Convert Pilot table Source 'cellRangeAddress' to a string address
SrcAddr = oConv.UserInterfaceRepresentation
msg=msg & " Found Pilot Table [" & oPilot.Name & "]" & CHR(10) & " Output range: " & OutAddr & CHR(10) & " Source range: " & SrcAddr & CHR(10)
next oPilot
If SrcAddr="" Then msg=msg & " No Pivot Table found" & CHR(10)
next oSheet
msgbox msg
end Sub
My shame. I was responding to LeroyG. Not to myself.