Why is Excel giving me error "Cannot shift objects off sheet"

Question

I have made a minor change to an Excel macro, and although it worked on the sheet I developed it for, it errors on a similar sheet.

The change simply adds a couple of columns, sorts the data by those columns, and then removes them again.

The only difference between the two sheets is that AutoFilter is turned on in the sheet that errors.

It fails with the error mentioned in the title on the line rng.Parent.Outline.ShowLevels RowLevels:=4

Answer 1

The error relates to moving an object onto a part of the screen that is not visible. For instance a comment or a graph object.

In this case, having autofilter on means that each column contains a combo box, as can be seen in ActiveSheets.Shapes. Something about adding or removing the new columns is obviously changing the behaviour, but it is not clear to me why.

Hopefully there is a better solution, but I have managed come up with this workaround which works even if it is not elegant.

1. Turn of AutoFilter
2. Remove all shapes from sheet
3. Do Processing
4. Turn AutoFilter back on again

which in VBA looks like

If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        s.Delete
    Next
End If

Do something interesting here...

If autoFilter = True Then
    rng.autoFilter
End If