Different Navigation Methods in Excel Macros
  Navigation (moving the cell pointer) using macros Back to Macro 
List of Topics
 
  Home
This page is a series of navigation macros presented in an abbreviated format in that detailed instruction is not presented. If you have trouble with the instructions provided, then review other exercises that present such detail. 

Exercise 1

Moving the cell pointer:
Turn on the macro recorder, with Use Relative References NOT checked. Click on any cell, or press an arrow key.  Stop the macro. Then activate the module containing the macro.  Your macro should look something like this: 

    Sub Macro1() 

        Range("A12").Select 

    End Sub

Note that when this macro is run, the cell pointer will always jump to cell A12 and that pressing an arrow, or clicking with the mouse when recording the macro, had no bearing on which direction you actually moved to get there, the cell pointer simply jumps directly to the same cell. This is Using Absolute References. Absolute meaning, you always want to go to this cell. This is very similar to the manual GoTo command (activate with Edit GoTo in the menu or pressing Ctrl +G or the F5 function key). 

Repeat the above steps with Use Relative References checked.  This macro should look something like: 

    Sub Macro2() 

        ActiveCell.Offset(1, 0).Range("A1").Select 

    End Sub

A totally different macro and all you did was click on a cell! This macro jumped the cell pointer with Relative Referencing. This means relative to the starting cell position.  Let's analyze this macro. First, we'll drop the Range("A1"), more on this later:
     ActiveCell.Offset(1, 0).Select
The offset command reads, move down one row and stay in the same column. If you want to move down one cell and to the right one cell, the offset would look like:  offset(1,1) 

What this step is saying is: from the active cell, move down one cell and select it.  You could replace the word select with Copy or one of many other commands. To say it another way: from the active cell, take action as it relates to the cell one row down and in the same column; that action is to Select it. The action could something else, for example Copy. 

Most of your macro commands will be in the Relative Referencing format, i.e. using the Offset("row,column").Select format. 

Examples:

Example 1
You have a large spreadsheet with various interest computations and the interest computations refer to a single cell containing the interest rate. Since this is always the cell you want to go to, then the Absolute Referencing method will work. So your macro may look like the first example above and as repeated here:
 

    Sub GoToInterestRateCell() 

        Range("D5").Select 

    End Sub

What if you later reorganize your worksheet and you have inserted a row above row five?  Now the macro will be taking you to the wrong cell.  The more preferable method of using this macro would be to name the cell and refer to the cell by name. So then, your macro would look something like: 
 
    Sub GoToInterestRateCell() 

        Range("IntRate").Select 

    End Sub

These types of navigation (GoTo) macros should be in this format, using range names, which are easier to interpret and future problems (resulting from things like inserting a row) are greatly reduced. 

Example 2
You are making entries in large list that is several hundred rows long and 40 columns wide.  You make entries in the next blank row and when you get to the last column, you want to jump the cell pointer down one row and to the left 39 columns, ready to make your next entry. You macro would look something like this: 

    Sub NextEntry() 

        ActiveCell.Offset(1, -39).Select 

    End Sub

This macro jumps the cell pointer as expected.  Note the negative 39 to jump to the left. 
 

It's time to get technical:

Let's look at this macro again 

     ActiveCell.Offset(1, 0).Select
Above, I used language like from the active cell, move down one cell and select it. I then said we were really performing action on the cell down one from the active cell. And the action could by Copy, Delete, Select, etc. 

The active cell is a Visual Basic Object and Select, Copy and Delete are Methods. The Offset is a part of the object. Therefore, this macro may be defined as follows: 

    Object.Object.Method 
    ActiveCell.Offset.Select
So now the language is: for the Object (the active cell), down one row, perform a Method, such as Select, Delete, Copy, etc. 

Don't worry too much about these definitions for now as they are a bit confusing. They are being introduced here, as most all VBA reference material uses this language. 
 

What happened to the Range("A1")?

Above, we dropped the Range("A1") from the macro. 

Discussion:

When you recorded the macro, it looked like this: 

    ActiveCell.Offset(1, 0).Range("A1").Select
As you get more experience in writing macros, you will most likely edit this to drop the Range("A1") so it looks like this:
    ActiveCell.Offset(1, 0).Select
Explanation: 

What the original macro is doing is defining the active cell as A1, i.e. the upper left of the current work area is the active cell.  To test this, edit the macro to read:

    ActiveCell.Offset(1, 0).Range("A5").Select
When you run the macro it actually jumps down five cells.  So what gives? The quick answer is that this is a poorly designed feature that does have some functionality, but in more advanced uses. Most all users drop it from the macro to avoid confusion. 
 

Example 3
You have a large list of data, let's say a database of daily sales figures. Today the list is, say, 100 rows long, tomorrow is could be 103, then 110 and so on. You desire to jump the cell pointer to the next blank row at the end of the list. First, how would you do this without a macro? You would: 
 

  1. Place your cell pointer within the list

  2. To ensure the next command will work
  3. Press Ctrl Down Arrow

  4. Jumps the cell pointer to the bottom of the list.  It stops on the last cell containing data
  5. Press the Down Arrow once

  6. To place the cell pointer on the next blank line

    With the focus only on the Ctrl Down Arrow portion of these steps, let's review the macro: 
     

       ActiveCell.End(xlDown).Select 
    This line would jump the cell pointer to last entry in the list. So how do we get to the next blank row? 
     
      ActiveCell.End(xlDown).Select 
      ActiveCell.Offset(1, 0).Select 
    In English, jump to the bottom of the list, then jump one more row down. 

    In order for the Ctrl Down Arrow keys to work (and the xlDown macro), the cell pointer needs to be in the list. So a line should be entered in the macro to force the cell pointer to jump inside the list. Assuming the you gave the first cell in the list a range name of ListTop, the completed macro would look like this: 
     

      Sub JumpToNextEntry() 
       
        Range("ListTop").Select 
        ActiveCell.End(xlDown).Select 
        ActiveCell.Offset(1, 0).Select 


      End Sub 

    Some comments on this macro: 
       
    Recording this macro with Relative References (instead of typing it) would result in the same commands. 

    Good macro writing takes into account most reasonable problem areas, therefore, if the first line is left out, the cell pointer will end up somewhere else most of the time as you will most likely forget to position it inside the list before executing the macro. 

    The first line is the Absolute Reference method mentioned at the beginning of this exercise, using a range name. 

    The cell reference inside the Range parentheses must be in quotes. This is true if the reference is a cell reference or a range name. (If the quotes are omitted, then the macro is looking for a variable with that name.) 

    The ActiveCell object can be replaced with Selection.  Selection means the cell or range of cells.  ActiveCell means just that, the active cell.  If a range is highlighted, the "white" cell is the active cell. Many times these mean the same thing, the cell pointer's position, but as you get more advanced, the differences get more significant. 

    Remember how the Ctrl Arrow works and the macro command End(xlDown). The cell pointer jumps down the list until it finds a blank cell, and it stops on the last cell with data. If your list has blank cells in the column that contains the cell pointer, this feature will not work as desired. It is a good practice to ensure the first column in a list contains no blank cells. Other articles discuss the details of this. 
     
     

Example 4

In Example 2 above, we had this following macro 
 

    Sub NextEntry() 

        ActiveCell.Offset(1, -39).Select 

    End Sub 

       
Taking what we learned in Example 3, let's revise this macro as follows:
 
    Sub NextEntry() 
     
      Selection.End(xlToLeft).Select 
      Selection.Offset(1, 0).Select
    End Sub
By using this approach it makes no difference how many columns are in the list, the cell pointer, jumps back to the first column, then down one row. This is, of course, if there are no blank cells in the row. 
 
 

Example 5
Now, let's get fancy.  The above examples placed each step of the selection process on a separate line. But we can combine them as follows: 
 

    Sub NextEntry()
      Selection.End(xlToLeft).Offset(1, 0).Select 


    End Sub

From the current cell pointer position, jump back to the left, to the first column in the list, then down one row. 
 
 

In Example 3, jumping to the bottom of the list: 

    Sub NextEntry() 
     
      Range("ListTop").Select 
      Selection.End(xlDown).Offset(1, 0).Select 
    End Sub 
Note how the End(xlDown) command is combined with the offset.select. What about combining the Range() with the rest of the macro? The two macros that follow are examples of what you may try, however neither one of them will work. 

This macro will not work as there is no object (such as Selection or ActiveCell):

    Range("ListTop").Select.End(xlDown).Offset(1, 0).Select 
This macro doesn't work as Range does not have a Selection property:
    Range("ListTop").Selection.End(xlDown).Offset(1, 0).Select
What all this means is that you can do a lot with the current selection, but with the Range as the object, as in this case jumping to a range, the commands must go on two or more lines. 

The term Object, Methods, and Properties are an integral part of VBA. Some commands can be more than one of these, depending on how they are used. For example, Range can be an Object or a Method.  This is covered in-depth in other articles. 
 

Related Topics: 
 

  • Range Names
  • Spreadsheet Navigation (without macros)
  • Selecting (highlighting) ranges with macros
  • A Quick Review of Different  Methods of Moving

  • the Cell Pointer with Macros
  • How to Record a Macro

  •  

 
 
 

 

 
 
Top

 
 
 
 
 
 
 
 
 
 
 
 

 

solutions@info-stat.com