How to Locate pivot tables

[JR729]

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

[LeroyG]

Moving the question from Français category to English.

[LeroyG]

@JR729

no pivot table is visible at all

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.

[JR729]

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

[JR729]

My shame. I was responding to LeroyG. Not to myself.