An annoying feature of Excel is not resetting the the used range. What is the used range ? - well, it's meant to be a square around all populated cells.

- How can you select the Used Range for this example ?

Step1: Press CTRL + END - This will select the last populated cell

Step2: - Now press CTRL + SHIFT + HOME

... all your data is now selected. Now, here's the annoying thing. Suppose we delete the last 4 rows, and press CTRL + END again. - the last cell that was populated (but isn't now) is selected.

To reset the used range, we need to resort to using VBA.

If this code is in the worksheet's macro module, and is run, pressing CTRL + END will now go to the correct cell.

Download workbook with code on how to reset the used range..

Check out this tip on how to view the worksheet's code if you are new to VBA

Training Video on how to reset the used range:

Loops are fundamental to programming, and in this tip, we'll look at FOR loops.

If you have never programmed before then this step by step guide will show you how to write your first program.

Q. What is a loop ?

A. I think this is best explained using an example.

Suppose you're at a farm, and a pig has just given birth to 10 piglets. The farmer asks you which is the biggest piglet. In order to answer this question, you will look at how big each piglet is, and once you have looked at all the piglets, you'll make your decision. A loop is the programming equivalent of looking at each piglet. Now lets use this example.

Step1: When you have the data as shown in the picture, Press ALT + F11 to bring up the VBA editor. Step2: You'll now need to insert a module, so right click on the blue area shown in the screen shot, and go: insert=>module

Step3: Now, in the white space provided, type the following code:

Step4: Click anywhere within the code, and press F5 - F5 runs code. You will now see a message

box showing which piglet is the biggest.

Download workbook to play with

VBA training Video - FOR Loops

Using VBA, there is the functionality for Excel to track most of what a user is doing to a workbook.

Armed with this information, a system designer can make things happen that help make the user's life easier.

Today, we're going to program a simple example of tracking selection change events. i.e. when you select something different from what is currently selected, an event is triggered.

Step1: Open a blank workbook, and press ALT + F11 - this brings up the VBA editor.

Now, double click on "Sheet1".. in blue in the picture.

- You are now in the code module of "Sheet1"

Step2: From the middle dropdown that says "General", select "Worksheet"

- VBA editor presents you with a subroutine that is designed to pick up the event that the selection has been changed.

Step3: In the empty line after "Private Sub", type the following: MsgBox "Hello" Press ALT + F11 again to return to sheet1, and press the DOWN ARROW to change the selection.

- You will now see the message box confirming that you have successfully trapped this event.

Here's an example sheet to play with

This functionality is used to great effect in my game of Noughts and Crosses, and in this Smart Table Sorting

Training Video - Worksheet Events