Worksheet dynamization

I appreciate some features that I have used in specific applications where, for example, the width of a column or the height of a row can automatically adjust based on the largest content in a cell, or where the row/column containing the cursor is highlighted. I was pleased to discover that, by using a few tricks, this can also be done in Excel.

Autofit rows/columns

First of all, it is necessary to access VBA, which can be done either by pressing the Alt+F11 key combination or right-click on the sheet tab and choosing the View Code option. (Fig. 1).

We will choose Worksheet, from the list of objects, which will also provide the prerequisites for the routine to be created. So, what’s remains to do is insert the code: Cells.EntireColumn.AutoFit. We will notice, when writing in a cell, that if we have more content than the initial width, after we press Enter the autofit will be done.

So, the final form for column width autofit will have the following content:

Highlight current row / column

Calling the same VBA steps to achieve this desired, we will need, in advance, a small conditional preparation. Astfel, dacă dorim să evidenţiem rândul pe care ne aflăm, vom selecta, în prealabil toată foaia de calcul So, if we want to highlight the row we are on, we will first select the entire sheet (if you want the effect to be applied to any accessed cell in that sheet).

We’ll go to `Conditional Formatting‘ and create a new rule: =ROW()=CELL("row"), of course, with the desired formatting (Fig. 3).

Then, as in the steps from the previous chapter, we go in VBA, to create a short subroutine, using a single action (Application.ScreenUpdating = True), as follows: