WS cells


In this module - Worksheet cells:

  1. Active Cell (WS) - the VBA ActiveCell object
  2. Selection (WS) - the VBA Selection object
  3. Current Region (WS) - the VBA CurrentRegion property of the ActiveCell

  1. Active cell
    • One cell in approximately 17 billion whose address appears in the Name Box
    • Also known as the Selected Cell, or Cell pointer (as distinct from the Mouse pointer)
    • If there is an Active Cell (and there normally is), then there is only ever one
    • The Active Cell is in the Active Worksheet of the Active Workbook
    • In Excel 2016 (and Windows 10), the Active Cell is shown by the dark green border with the fill handle (if there is only a single cell selection)
    • The Active Cell might not be visible as happens when the User scrolls the worksheet, but the address or name of the Active Cell will appear in the Name Box (in Ready mode)
    • In Ready mode, the Active Cell is the one ready for input (typing in the cell)

    • Changing the location of the Active Cell
    • The Active Cell can be moved:
      • with the keyboard arrow keys, or
      • Enter: down one row (depending on WS settings)
      • Tab: right one column
      • Shft + Enter: up one row
      • Shft + Tab: left one column
    • Pressing Ctrl + Home moves the Active Cell to the A1 or R1C1 address on the WS, the Home cell
    • Use the mouse to click or select a cell. This cell is now the Active Cell

  2. Selection
    • The Selection can be a Cell (on the Top layer) or a WS object on the Drawing layer (such as a Chart, Drawing, or Equation)
    • A single cell Selection is the Active Cell (Selected Cell)
    • There is no Selected Cell if Drawing layer objects are selected
    • A Selection can be multi-cell (the Selected range). Contiguous (rectangular), or non-contiguous (irregular or non adjacent)
    • A Selection of cells includes the Active Cell
    • In Excel 2016, the Selection is shown by the dark green border with the fill handle
    • In multi cell selections, or multiple selected ranges, the Active Cell is shown by the contrasting fill color within the selection
    • To select multiple cells, use Shift + Arrow key(s) on the keyboard, or Click and Drag with the mouse. The Active Cell is in the cell where you begin to Drag from (one of the corners)

    • Navigation
    • The position of the Active Cell within the Selection can be moved with navigation short-cut keys.
      • Ctrl + . : cycles the four corners of the Selection in a clockwise direction
      • Enter: Down one row
      • Tab: Right one column
      • Shft + Enter: up one row
      • Shft + Tab: left one column
    • Non-contiguous multiple cells can be selected with the mouse Ctrl + Click and Drag, typing the addresses in the Name Box, or the Go To dialog box

  3. Current region
    • The Current Region is range of data (non-blank cells) that surrounds the Active Cell
    • The perimeter of the Current Region is itself bounded by blank rows and columns
    • With the Active Cell in a Data Region, activating the Data > Sort & Filter > Sort dialog box will select the Current Region (in Ready mode)

    • If the Active Cell is in a list, table, or data base, then press Alt + A to select the current region, or Home > Editing > Find & Select > Go To Special > Current Region on the ribbon

    • In figure 1, the User has selected a range (shown by the grey back color), excluding the header row, then moved the Active Cell to cell H10. When the Create Table command is run, the Create Table dialog box shows the address of the Selection and the WS mode switches to Point mode. The Selection has a marching ant border

    • Marching ants border (in Point mode)
    • Marching ants borders are visible with the following Dialog Boxes such as: Create Table, and Create PivotTable
    • xlf-marching-ants-v3
      Fig 1: Marching ants - Point mode - using the arrow keys, the Point mode Selection can be seen to be independent of the User Selection
    • If the exercise is repeated with only the Active Cell as the Selection, then Create Table command applies the marching ant border to the Current Region