Wim‘s Excel, TM1 & soccer site

The selection pane for objects

Apr. 27, 2019

	Example files with this article:
	The Excel selection pane

Introduction

Hi, it's me again with a new article on Excel ! I would like to show you the selection pane in Excel, and how it can help you spot things you wouldn't see otherwise. I will give you tips and tricks to understand some reasons why your beloved Excel file becomes huge in file size.

The case goes as follows. I have an Excel file at hand containing crossword puzzles. One excerpt from the file is a sheet containing the solutions. Sorry to all of you who don't understand Dutch, the puzzles are in Dutch ! But that's not what I wanted to talk about today.

You can download my (big) file at the top of the page. When you open the file you will see just the solutions to many crossword puzzles. You will see the rosters in the columns A to R, as well as 2 buttons:

Granted, the file is not tiny and contains about 170 crossword puzzles along with some formatting (borders) to make them look like puzzles. However, this cannot justify the file size of about 2 MB. There's got to be something else out there.

What could it be ? If I press Ctrl + End, I will end up here: cell S2407.

Is that far out to the right and far to the bottom ? No. If it would be the case, this can lead to file bloat and a bigger-than-necessary file size since Excel will also take into account all cells until that 'last cell'. Even if you think that you only use few cells, Excel might think differently ! The solution would be to delete unnecessary rows and columns, save your file and you should see the file size drop again. Here a big 'used range' is not the culprit however.

What else could it be ? Thens of thousands of VBA code lines ? No since the file is xlsx, which is a macro-free file format. Excessive formatting and conditional formatting ? No, there ain't any conditional formatting and regular formatting is very limited too. Hidden sheets ? No, there aren't any. Links to other files ? A ton of named ranges ? Not the case. Pivot caches that are still there in the file ? Again nope. Let's look at objects and shapes !

In fact, the user of this file fills out the crossword puzzle solutions based on a picture of the solutions on the internet. As a matter of fact, the user copies the solution as a bitmap to the sheet and types the solutions in the rosters based on the picture. While copying from the internet I suspect that other smaller objects are copied too to Excel, while still being transparent (invisible). Afterwards, the invisible pictures are not removed so many of them are still lurking around. These are the real culprits of the huge increase in file size !

There you go. Many invisible picture files that accumulate in size and are part of your workbook. How can we find them in the sheet ?

The first step is showing the "Selection pane":

Then you can select the objects (pictures) and make them visible (though most of them are already transparent). But what you will most probably want to do is checking out where these objects really are in the sheet. You can do that by pressing the Tab key, or if you want to cycle through them in the opposite direction, it will be Shift + Tab. Now you can delete the shapes (pictures).

If you want a VBA-solution, here is some code for you. It will cycle through all the shapes in the worksheet. Remember that buttons in the sheet (that you don't want to delete), or data validation, or filter buttons, ... these are shapes too. So watch out for you might delete too much !

Sub Loop_through_shapes()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Code to loop through shapes
' 27/04/19
'''''

    Dim s                     As Shape

    For Each s In ActiveSheet.Shapes
        Debug.Print s.TopLeftCell.Address(0, 0), s.Type, s.TextFrame.Characters.Caption
    Next

End Sub

My code will dump the shape details to the 'Immediate window' in VBA for you to have a look at them. First you get the cell where the shape is located, then the type of shape and lastly the text on the shape (the 2 buttons in the sheet are shapes and so the text on the button is retrieved). Add s.Delete instead of the Debug.Print line if you want to get rid of the shapes but don't delete too much please.

A small exercise for the reader: who of you can explain the whitespace (empty line) in the Immediate window below the line here:

However, another tip is to definitely use the F5 key (or Ctrl + g). If you then use Special... you can ask for the Objects in the selection or the whole sheet. Watch this, step 1: