Selecting a Block of Cells with an Excel Macro

The information in this article applies to:

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

In Microsoft Excel it is possible to create and use a macro to select a block of cells starting at the current active cell and ending with the last cell in that block.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements.

The following Microsoft Excel macro selects a block of cells starting with the current active cell on the Worksheet and ending with the last cell of the block.

   =DEFINE.NAME("a", ACTIVE.CELL())
   =SELECT.END(4)
   =SELECT.END(2)
   =DEFINE.NAME("b", ACTIVE.CELL())
   =SELECT(!a:!b)
   =RETURN()

   A1: 23   B1: a   C1: 2   D1: de
   A2: 24   B2: b   C2: 3   D2: fg
   A3: 25   B3: c   C3: 4   D3: hi
   A4: 27   B4: d   C4: 5   D4: jh

To use this macro, the block of information cannot have trailing information in the first column (in the example, Column A Row 5). This is because of the nature of the SELECT.END command. SELECT.END(4) will select down Column A until it finds a blank cell. SELECT.END(2) will select across Row 5 until it finds a blank cell. Since all cells in the row following Cell A5 are blank SELECT.END(2) will select from A5 until the end of the spreadsheet (IV5).

The DEFINE.NAME command is used in preference to the SET.NAME formula. This is because the SET.NAME statement will define the name (like the sheet command Formula, Define Name) to the Macro sheet and DEFINE.NAME will define the name to the active sheet.