Run-time error '1004': Unable to set the Visible property of the PivotItem class

There are two circumstances in which this error will arise when issuing a [PivotItem].Visible = True command in Visual Basic:

1. The sort order of the PivotField containing the PivotItem is set to anything other than xlManual; and,
2. The ShowAllItems value of the PivotField containing the PivotItem is set to False and an attempt is made to set a PivotItem containing no data (i.e. [PivotItem].RecordCount = 0) to True.

To process [PivotItem].Visible = True safely you should issue the following commands first:

[PivotField].AutoSort xlManual, [PivotField].SourceName
[PivotField].ShowAllItems = True

Excel formula to detect if a row is visible or hidden

A number of times over the years I've been looking for a way to tell if a cell in Excel is on a row that is hidden without having to resort to VBA.  Now that I've found it it seems blindingly obvious.  I share it here just in case someone else might not have seen it.

    A   B                                                                                  



1 1


2      =IF(SUBTOTAL(109,A1)=1,"Visible","Hidden")


 
If row 1 is visible B2 will display "Visible" otherwise it will display "Hidden".
 
Now I just need to wait until the need arises again before I get to use it.  I can't remember what I was doing or why I needed it but I'm sure it'll come up again!